Hello everyone,
I have been having a bit of trouble making this chart and was hoping for some suggestions.
The chart is supposed to get sales history for all locations over the last five years, and present the total sales dollars for each location broken out by year and then month. The goal is to calculate the yearly sales and then look at each months sales and calculate the percentage of the total that month made up.
I am using a pivot table with Location, Year, and Month in the dimensions, and this presents the table how I want with the each store broken up into years and then each year into months. Then this expression is used to get the sales:
SUM({$<SalesRecordType={'Sales'}
, Key|Date={">=$(=[T6Y_vToday])<=$(=[T6Y_End])"}
>}Sales)
Where I am running into issues is calculating the percent of yearly sales for each month. I tried using this expression:
SUM({$<SalesRecordType={'Sales'}
, Key|Date={">=$(=[T6Y_vToday])<=$(=[T6Y_End])"}
>}Sales)
/
AGGR(SUM({$<SalesRecordType={'Sales'}
, Key|Date={">=$(=[T6Y_vToday])<=$(=[T6Y_End])"}
>}Sales), Location, Year)
I want it to be something like this (I didn't fill this in all the way, but this is the general idea).
| Location | Year | Total | Month | Jan | Feb | Mar | Apr | |
|---|
| 2012 | $500 | | 200 | 100 | 100 | 100 | |
| Store1 | 2013 | $500 | | 200 | 100 | 100 | 100 | |
| | | | 40% | 20% | 20% | 20% | |
Dividing the monthly sales by an aggregation of Sales, Location, and Year. But it does not show the correct number, it averages it across all locations selected and not each Location. I cannot seem to get the Aggr to work correctly. Any help would be appreciated!
