sap-dashboards-ranking-header

Ranking in SAP Dashboards

I was recently working on a Personal Finance Dashboard and had to rank the returning data in descending order based on spending. I know this can be done on the query side using the sort functionality, however, I was trying to apply ranking on a subset of data returning from a main query, which was not in the desired sort order. In the end it was pretty straight forward.

Ranking in SAP Dashboards

XLF File

Download the XLF file and refer to it as you go through the steps

Source Data (B9:D204)

For demo sake, I’ve imported a small sample source data into the Excel model. This is the same data as that of the main query that I was talking about earlier.

sap-dashboards-ranking-source

 

Filtered Rows (G10:I26)

Drop in any Selector component with “Filtered Rows” capability. Configure it to insert filtered rows based on “Month”. Since the number of categories might be different for each month, picking the number of destination rows might be a little tricky. A simple rule is to analyze the source data a pick a month with maximum categories and use that number for destination rows.

sap-dashboards-ranking-filtered

 

Rank Formula (F10:F26)

Apply the RANK formula on the “Spending” column of Filtered Rows.

See also: Co-Author Tip

sap-dashboards-ranking-formula

Hard Coded Rank (K10:K26)

Hard code numbers from 1 through N (Max no. of categories). We will use this to look up values from the filtered rows range.

 

Display Data (L10:M26)

Lastly, apply VLOOKUP using the Hard coded numbers as “value” and the Filtered Rows as “table_array”. Do this to get both “Category” and “Spending”.

sap-dashboards-ranking-vlookup

There you have it!

Do you know a better way to do this? Please use the comments section to share your trick. Thanks!

Related Posts:

Mark Bradley liked this post
  • http://twitter.com/xjhacking Xavier Hacking

    Hi Kalyan, why don’t you just use the ‘Enable Sorting by data’ option in the charting components?

    • kalyan verma

      Yes, that works perfectly for charts. The issue is with the Spreadsheet/Scorecard components.

  • http://twitter.com/josh_tapley Josh Tapley

    Don’t forget that you need to modify that Rank formula a bit or it won’t be able to break ties.  

    =RANK(I10,$I$10:$I$26,0)+COUNTIF($I$10:I10, I10)-1

    I actually find myself doing this often when I’m overlaying a bar chart on top of a spreadsheet component.  Especially since the Bar chart puts the data in reverse order!

    • kalyan verma

      Good one Josh. Thanks!

  • Rvrs

    Hi

    can you suggest me a good xcelsius2011 book for a beginner .Is it easy for me as i don’t have knowledge of previous xcelsius products. 

  • Gaurav

    I am using 6.0.4.0 version and Build 14,0,4,738 ,but i am not able to open this xlf file