0 Replies Latest reply: Aug 29, 2016 1:45 PM by Jason Campbell RSS

    Sum(max(YearMonth)) for each year

    Jason Campbell

      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.