Beastmode- Use date range as a calculation

bof

We are a restaurant company and like to look at "Items sold per day Calculation".

It is a pretty straightforward beastmode to get that. (Total Quantity) / (Count distinct date of business * count distinct stores).

However, if the item does not sell in a day then it is not accounted for. 

Example, we sell 4 of 'Product A' on Day 1, 0 of Product A on Day 2. The calculation would show the item sold per day as 4 rather than 2.

 

A couple of thoughts I've had but wont work. Cross joining our items table to dateofbusiness/stores so 0's can be accounted for.... won't work, dataset is already HUGE

Using a max(dateofbusiness)-min(dateofbusiness) to get the number of days. Again, may be closer but still not ideal.

 

The ideal solution would be to have a denomitor that equals "Number of Days being Analyzed" that works with the date range.

ckenimer

Have you deteremined a good way to calculate this? We are also looking towards a similiar issue. I would rather not "hard code" or force concrete date ranges

bof

I have not come up with a solution. Was hoping this thread could get some "likes" to have DOMO look into it.

user050653

Having the exact same issue. Looking for average over all days, but only returning average based on days that have activity.

jaeW_at_Onyx

? where's the duplicate question button? ? 
https://dojo.domo.com/t5/Data-Sources-and-Connectors/Available-work-day/m-p/50697#M3643