Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Contributor III

Sum(max(YearMonth)) for each year

I have a pivot table with an ID, Year, and several metrics. 


            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.


  • - 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))


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.

Community Browser