1 Reply Latest reply: May 3, 2013 6:43 PM by Stefan Wühl RSS

    Set Analysis with Rank() Function

      Hello,

       

      I have a straight table which has a single row per location, and then obviously some metrics per each location.  I am trying to add a Rank column which ranks each location against a certain metric.  If I create an expression as below;

       

      =Aggr(Rank(Sum({1}[TY Net Sales Dollars])),Location)

       

      This works great, and ensures that even if I make a selection which limits the number of locations visible in the straight table, the rank is still the rank across all locations.

       

      The problem is, I don't want to rank on just TY Net Sales, I want to rank on YTD Change TY vs LY, for which I have an expression working correctly;

       

      =(sum({$<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [TY Net Sales Dollars])

      / sum({$<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [LY Net Sales Dollars])) - 1

       

      It is my understanding that this {1} in the first expression is what causes it to always rank across all locations, so my question is how can I keep this in my rank expression, while using the calculation in my second expression instead just "TY Net Sales Dollars"?

       

      Thanks!

      Chris

        • Re: Set Analysis with Rank() Function
          Stefan Wühl

          If I understood correctly, just replace the set identifier $ (i.e. the current selection set) with 1 (the full record set):

           

          =(sum({1<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [TY Net Sales Dollars])

          / sum({1<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [LY Net Sales Dollars])) - 1

           

          -->

           

          =Aggr(Rank(

          (sum({1<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [TY Net Sales Dollars])

          / sum({1<[Fiscal Year Id] = {2012}, [Fiscal Month of Year Id]={'<=$(vMaxMonthOfYearId)'}>} [LY Net Sales Dollars])) - 1

          ),Location)