topic Re: Ranking with filtering in App Development

Ranking with filtering

kcville — Sat, 16 Nov 2024 01:58:31 GMT

Hello

I have a large table with names, departments and a field with a measure.

I would like to Rank the measure for each name after selecting a department. But I want to maintain the Rank result when I filter on an individual name.

Currently what happens when I select a name the table is filtered and the Rank result goes to 1 as there is the only row.

I want to Rank on the underlying data rather than what is in the table, I know I could do this in script but I have lots of departments and lots of measures so would be too complicated.

I hope this makes sense.

Kelly

Re: Ranking with filtering

Taoufiq_Zarra — Mon, 06 Jul 2020 09:34:28 GMT

Hi,

can you sahre a sample and the expected output ?

Re: Ranking with filtering

kcville — Mon, 06 Jul 2020 09:41:36 GMT

Example data:

Name Department Sales Rank

John Sales 10 1

Peter Sales 11 2

Paul Sales 12 3

I would like to filter to Paul and see the following:

Name Department Sales Rank

Paul Sales 12 3

What I see at the moment

Name Department Sales Rank

Paul Sales 12 1

Re: Ranking with filtering

tresB — Mon, 06 Jul 2020 09:56:19 GMT

If you are using any aggregation function for calculating rank, you could use set analysis to disregard the selection and keep the rank fixed. like:

Rank(TOTAL Sum({<Name>}Sales))*Avg(1)

Re: Ranking with filtering

Taoufiq_Zarra — Mon, 06 Jul 2020 09:59:24 GMT

If I consider the following example:

Data:
load * inline [
Name , Department ,Sales

John , Sales , 10

Peter , Sales , 11

Paul , Sales , 12
];

In dimension :Name, Departement

Expression: sum(Sales)

aggr(rank(total sum({1} Sales)),Name,Department)

for rank

output :

Re: Ranking with filtering

kcville — Mon, 06 Jul 2020 11:38:14 GMT

Thank you both so much, I used a combination of both your methods...

aggr(rank(total sum({<Name>} Sales)),Name, Department)