3 Replies Latest reply: Oct 9, 2016 7:35 PM by Sunny Talwar RSS

    Set Analysis Max Report Year where report year is not null

    james eckstein

      I have a table like this.

      IDYearAmount
      AAA2012300
      AAA2013400
      AAA2014500
      BBB201250
      BBB201360
      CCC20121

       

      I am looking for set analysis that will sum Amount where year is max. I am currently using this calculation in a KPI object.

       

      Sum({$<[Year]={'$(=Max([Year]))'}>}[Amount])

       

      The result is 500. This is because the Max(Year) for the entire group being summed is 2014 and there is no data for 2014 for ID "BBB" and "CCC"

       

      I need the result of 561

       

      AAA    2014     500

      BBB    2013     60

      CCC   2012     1

       

       

      What is the set analysis i need.