Sum(max(YearMonth)) for each year

jcampbell474 — Mon, 29 Aug 2016 17:45:53 GMT

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.

topic Sum(max(YearMonth)) for each year in QlikView

Sum(max(YearMonth)) for each year