Rank Error with Multiple Dimensions/Lists

jmathew18

I am building a module that is ranking the Cost Centers for the Corporate segment based on the amount of Expenses per Employee (USD) that it has accumulated in 2023 for the Travel & Entertainment account and the Employee Relations account separately. The time dimension includes YTD, QTD, and Month. I want the rank to adjust based on the YTD, QTD, and Month for time and also change based on the account selected.

There is an error with the Rank line item, and it is not ranking sequentially starting from 1.

This is my formula: RANK('Expense per Employee (USD)', DESCENDING, SEQUENTIAL, Segment Text = "Corporate")

Please assist on the best method to go about the RANK function. Attached is a screenshot of my module in the DEV environment. In the PROD environment, the Expense per Employee (USD) line item is populating the data accordingly so that is not an issue.

Thank you!

HimanshuRaj

Hi @jmathew18

There is a 5th parameter in Rank function which is group by. Your use case would need to use group by functionality as well to give the output you need

https://help.anaplan.com/rank-a5f5778e-5e88-48ad-96ad-715178cda9b2

Cheers!!

jmathew18

Hi @HimanshuRaj, thank you for your suggestion. I used the Segment Text = "Corporate" as the GROUP BY Condition in the formula, since "TRUE" is already the default value for the 4th condition. Also, I used this as the GROUP BY condition because I have two segments in the module other than Corporate, and I want each segment to have its own independent rank.

I changed my formula to the following to make it more clear but it is still outputting the same numbers for Rank as it did before: RANK('Expense per Employee (USD)', DESCENDING, SEQUENTIAL, TRUE, Segment Text)

Do I need to make a separate systems module to implement the Group By?

Thank you!

HimanshuRaj

In your screenshot, I can also see one more dimension "Travel & Entertainment". You'd need to include that as part of Gorup by as well.

RANK('Expense per Employee (USD)', DESCENDING, SEQUENTIAL, TRUE, Segment Text & "List 2 Text")

Cheers!!

jmathew18

@HimanshuRaj

Thank you! How would I Group By the time? I want to group it by Month, then QTD, and YTD. Do I have to make a separate module for this?

HimanshuRaj

@jmathew18 Individual time is a block in itself. Apr is treated as a different group then May. So we don't to apply a group by Time.

For your use case, I'd say create separate modules by month, QTD, YTD respectively and use same rank formula as used above in all of them

Cheers!!