I've got two tables in my data model:
- Sales data by location, product, and fiscal month
Fiscal Month | Location ID | Prod ID | Sales | 201802 | 1 | 1 | 20 | | 201803 | 1 | 1 | 0 | | 201804 | 1 | 1 | 55 | | 201805 | 1 | 1 | 14 | | 201806 | 1 | 1 | 28 | | 201807 | 1 | 1 | 0 | | 201808 | 1 | 1 | 0 | | 201809 | 1 | 1 | 38 | | 201810 | 1 | 1 | 0 | | 201811 | 1 | 1 | 0 | | 201812 | 1 | 1 | 12 | | 201901 | 1 | 1 | 11 | | 201802 | 2 | 1 | 0 | | 201803 | 2 | 1 | 0 | | 201804 | 2 | 1 | 1 | | 201805 | 2 | 1 | 2 | | 201806 | 2 | 1 | 9 | | 201807 | 2 | 1 | 0 | | 201808 | 2 | 1 | 0 | | 201809 | 2 | 1 | 0 | | 201810 | 2 | 1 | 4 | | 201811 | 2 | 1 | 0 | | 201812 | 2 | 1 | 4 | | 201901 | 2 | 1 | 6 | |
|
- Fiscal month and week count
Fiscal Month | Week Count |
201802 | 4 | |
201803 | 5 | |
201804 | 4 | |
201805 | 4 | |
201806 | 5 | |
201807 | 4 | |
201808 | 4 | |
201809 | 5 | |
201810 | 4 | |
201811 | 4 | |
201812 | 5 | |
201901 | 4 | |
My goal is to find the average weekly sales, based on the time-period selected by my users. I get a number that’s too low because it’s adding up the Week Count for all rows in the data model when I use
Sum(Sales) / Sum(Week Count) = 1.96 units/week
What I need to end up with is the sum of the Week Count for each unique month in the selected time-period, or if no time is selected, then for the entire period in the app (3.92 units/week).
