Boolean Filter for Salary Rank function

johan.marketoft

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

ChrisAHeathcote

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.

VIGNESH.M

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

@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.

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.