Rank function

Hi Everyone,


I am trying to use the rank function in Pyramid (similar to rank function in excel) to rank customers with highest revenue to lowest. The environment used is MS OLAP. 


The Rank function in Pyramid says:

Rank(<data point>,<set>,<numeric data point>)


Please explain how to use this to rank customers having highest revenue for a particular period (period given under filters)




2 replies

    • Dvir_Buzaglo
    • 4 yrs ago
    • Reported - view

    Hi Manshi,


    I've created the next example to explain how the Rank function is working in Pyramid.

    I've created a formula and use the "Rank" function from the Tool panel>Numeric section
    Rank(<data point>,<set>,<numeric data point>).

    1. In the first part (Data point), drag the data point node and select the required hierarchy current member as described in the next image:
    2. In the second part select the  "Model attribute" node to the formula and select the hierarchy.

    3. By writing Currentmember.siblings you will rank members of the hierarchy relative to their siblings
    4.  In the third part of the formula select the measure you want to rank by.

      This is how it looks in script mode:
      Rank([Customer].[Customer Geography City].CurrentMember,[Customer].[Customer Geography City].CURRENTMEMBER.SIBLINGS,[Measures].[Sales] )
    5. In the report, select the customer hierarchy on rows, the RANK formula on columns and the time hierarchy on the filter (I've added the ranked measure as well).
      As you can see when drilling down to Germany, all Germany cities are also ranked in comparison to their level (city) siblings.


    Hope this help.

    • Manshi_Dhanesha
    • 4 yrs ago
    • Reported - view

    Dvir Buzaglo

    Hi Dvir,

    Thank you so much. This helped really. I have modified it a little to meet the requirement.



Content aside

  • Status Answered
  • 4 yrs agoLast active
  • 2Replies
  • 36Views
  • 2 Following