I have 3 tables in an app as follows:
- Location (information about each location)
- Current Sales (sales by location, by day, by hour for the last 28 days)
- Last Year Sales (sales by location, by day, by hour for the last 28 days - 1 year ago)
I created a Key field in both of the Sales tables to connect them:
Location & '-'& Date(TicketDate,'YYYY-MM-DD') &'-'& Hour(TicketTime) as Key
This works, but the problem is when a location doesn't have sales during a specific day/hour in both time frames.
I end up with a table where the sales that don't match are on a seperate line. This throws off the year-over-year difference calculation, which I calculate using the following formula:
(Sum(Amount) / Count(distinct Total Location)) / (Sum(LYAmount) / Count(distinct Total LYLocation)) -1
I need to be able to compare any time frame within the 28 days (all 28, 1 day, 1 week, etc, plus specific hours and groups of locations), but my year-over-year calculation is being thrown off when a location does have sales in the same hour and same day.
What I want to have happen is the sales from the current year divided by the number of locations is compared to last year sales for the same time frame divided by the locations. For example, if 1,000 locations had sales yesterday at 3am the total sales for 3am would be divided by 1,000 to get a per location amount. Then if 1 year ago 980 locations had sales at 3am, those total sales would be divided by 980 and the two per locations amounts would be compared and a percent difference would be calculated.
