Avg Sales per day - based on different days of work
I am working on Retail Sales data.
In some cities the stores works 7 days whereas in some they work 6 days a week.
So when I do an average sales per day for a month calculation it should take into account the operational days to calculate. Let say for the month of January 2018; for Cities where the stores are closed on Sunday, the number of days is 27 whereas for other cities the number of days is 31.
I would expect to see a SalesDate field in Sales table.
If your Sales table show such a field, do you really have entries for a store on a date without sales?
If not, do you have an entry for each store on each sales date with either a value >0 or zero (which I assume is kind of unusual, to have no sales at all on a given date, but this might depend on your stores & business). In other words, are there any cases with no records for a store but operational on that date?
If your sales table shows a record per store and operational date, you should basically be fine.
the average formula is to divide the sales by number of working days during the month for that store. (in your suggestions; if there is no sales on some days; the avg will show higher figures which is not correct).