4 Replies Latest reply: Jan 19, 2018 8:33 AM by Sunny Talwar RSS

    Rank Caluculation Using aggr() functions

    Anindya Manna

      Hi all,

      I am calculating rank in a table. my report looks like after selecting a dimension like below.

       

      XYYTD salesXY RankY rank
      9000119
      8000227
      7000583
      6000
      5000

      Rank calculation is done based on this below expression.

       

      XY Rank = Aggr(Nodistinct rank(sum({$<[Process Date]={">=$(=YearStart(Max([Process Date]))) <=$(=Max([Process Date]))"},

      [Month ID]=>}[Sales Amt]),4),X,Y).

       

      Y Rank=aggr(Nodistinct rank(sum({$<[X]=,[Month ID]=,[Process Date]={">=$(=YearStart(Max([Process Date]))) <=$(=Max([Process Date]))"}>}[Sales Amt])),[Y]).

      But the problem is when I am selecting other dimension like Month Id the rank calculation is not based on this particular month so by selecting a particular month rank is showing 1,2,3,6,9,12, like this as other position not part in this month so they removed.

      But I want continuous rank based on each dimension selection.