Show days with 0 sales

[jobrien4]

I'm trying to build a query that adds up the total sales for a given product per date. The problem I'm running into is some products have $0 sales on a given day and don't appear in the table I'm querying. How would I get the query to add a record for that day for the given product with 0 in the sales field?

To give more details, I currently have one table showing the sales data. It has a record for every sale that includes the product of the sale, the amount, and the date. In the query, I'm summing the sales for a given day and product. Then I plan to export to Excel where I will run additional analysis.

If there are no sales for the product in a day, I'd like it to list the date and product with a $0 in the sales column.

 

[jdraw]

Why exactly do you have to record a sale that doesn't exist/didn't occur?

 

[jobrien4]

Eventually in my analysis I need to show the average 3-day sales amount when I shoot it into Excel. So if there is $300 on 8/1, $0 on 8/2, and $100 on 8/3, I want to be show the 3-day average as (($300 + 0 + $100)/3). Right now, it would skip over 8/2 then include 8/4.

 

[jdraw]

I'm not very familiar with Excel, but it seems to me if you start on 8/1 and you go for 3 days (0 based), you would take the sum of values for dates starting 8/1 and ending 8/3 and divide by 3.

Another choice is to fill cells for all dates involved with 0s. Then update the cells for the dates for which you have no zero values. Seems at the end you will have all dates properly recorded for your purposes.

Good luck.

 

[jobrien4]

I ended up managing in Excel.

I created a column that has each business day in it. Then did a vlookup to the query results that gave me the total sales if there was a sale that day. I added an iferror(vlookup(),) so that days without sales returned a 0.

 

[jdraw]

Good stuff. Glad you have it sorted out.