Boolean Filter for Salary Rank function

johan.marketoft
Frequent Contributor

Boolean Filter for Salary Rank function

Hi,

 

I want to only show the top 10 earners in each department (List hierachy E1 Department is top and E2 Employee below).

I have created a RANK that will give organize numbers lowest to highest based on each department, But the numbers do not "start over" for each department. So there is only one department, that has RANK 1, 2, 3 values etc. Then highest salary in another department starts on higher value but still fine as it is lowest rank in that department. But the problem is I now cannot do a filter based on 1-10. Do I need to update the RANK formula or can I somehow come around this with a boolean formula?

 

Best regards,

Johan

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHeathcote
Community Boss

Re: Boolean Filter for Salary Rank function

Try adjusting the RANK formula to something like;

=RANK(Value to Rank, DESCENDING, SEQUENTIAL, TRUE, PARENT(ITEM(E2 List)))


It is the last part that should direct the ranking to restart for each department.

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

3 REPLIES 3
VIGNESH.M
Certified Master Anaplanner

Re: Boolean Filter for Salary Rank function

Hi Johan,

 

You have to Rank the Salary by the department(L1) to start over the ranking for each department.

In the system module, first find the L1 of each employee and use it for ranking.

The formula will look like this,

RANK(Salary, DESCENDING, SEQUENTIAL, TRUE, L1)

 

Check the Rank function in Anapedia for more detail.

https://help.anaplan.com/en/4f94f2e7-e7d9-4bae-ba6f-2b872e8d9189-RANK 

 

Regards

Vignesh M

JaredDolich
Moderator

Re: Boolean Filter for Salary Rank function

@johan.marketoft 

Great use case!! Adding to @VIGNESH.M point, I did something similar, where I only wanted the top department for each category. Here's how it looked.

This is the final chart. I only want to see the top group in each category.

GroupRank1.png

Here's the module that powers the grid: Note I use rank but at the category level, so each category gets its own ranking of groups. Then I just use the Booleans, as you suggested.

GroupRank2.png


Jared Dolich
ChrisHeathcote
Community Boss

Re: Boolean Filter for Salary Rank function

Try adjusting the RANK formula to something like;

=RANK(Value to Rank, DESCENDING, SEQUENTIAL, TRUE, PARENT(ITEM(E2 List)))


It is the last part that should direct the ranking to restart for each department.

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post