I have a pivot table with an ID, Year, and several metrics.
Year
Metrics (sPIF)
ID Values
I need one expression that sums the sPIF (by ID) for each year (2013-2016), by month. If no YearMonth’s are selected, then I need the sPIF sum of each Year’s max(YearMonth). Current Year would be 201608. If a YearMonth is selected, I need the sum of the same YearMonth 2013-2016. All months cannot be summed, just the max YearMonth for each year.
Examples:
- - YearMonth selections = 201606. 201606 sPIF is summed. 201506 is summed. 201406 is summed. 201306 is summed.
- - YearMonth selections = 201510. 201608 sPIF is summed (201608 is the max(YearMonth) in 2016). 201510 is summed. 201410 is summed. 201310 is summed.
- - YearMonth selections = Nothing selected. Default view – 201608 sPIF is summed. 201512 is summed, 201412 is summed, 201312 is summed.
Here are a few expressions that I tried:
If(Left(max(YearMonth),4)>='$(vMaxsYear)' and GetSelectedCount(YearMonth)=0,sum({<YearMonth={"$(=max(YearMonth))"}>}sPIF),sum(sPIF))
If(right(max(YearMonth),2)>='$(vMaxsMonth)',sum({<YearMonth={"$(=max(YearMonth))"}>}sPIF),sum(sPIF))
sum({<sMonth={"$(=max(sMonth))"}>} sPIF)
sum({<YearMonth={"$(=max(YearMonth))"}>} Total <sYear,AgtId> sPIF)
sum({<YearMonth={"$(=max(YearMonth))"}>} sPIF)
Sum({<YearMonth = {'$(vYrMth)'}>} sPIF)
Sum({$<YearMonth={$(=if(max(YearMonth)=max({1}YearMonth), Date(AddMonths(Now(),-1),'YYYYMM'), max(YearMonth)))
I scoured the forums, but cannot find anything to apply. Any/all help is getting the correct expression will be greatly appreciated.
