topic Re: Filtering with Static Ranking using Set Analysis in QlikView

Filtering with Static Ranking using Set Analysis

ruzvmun — Fri, 04 Dec 2020 06:17:12 GMT

Hello all,

Really short on time, apologies if solution to this already exists and I hope I explain this well.

I am trying to create a ranking that is static even after selections/filters.

The expression I currently have is:

Aggr(ceil(rank(sum(ORG_SALES)/sum(GLOBAL_SALES))),SalesMan)

The filtering is applied on a hierarchy as below

1.Team

2.District

...with SalesMan in the expression coming under District in the hierarchy.

By default, the ranking should show for all Teams or entire company.

Selecting Team -> District should limit the number of records but the ranking should stay the same

Can someone assist me, I apologize I dont have sample data, trying to get this out ASAP

Thank you in advance,

Re: Filtering with Static Ranking using Set Analysis

chrismarlow — Fri, 04 Dec 2020 13:31:16 GMT

Hi,

You can try cancelling the selections on the rank through set analysis & then multiplying by Avg(1) (to which selections apply), so something like below from a toy set of data.

No selections;

Then with a selection the ranks are the same;

Cheers,

Chris.

Re: Filtering with Static Ranking using Set Analysis

ruzvmun — Fri, 04 Dec 2020 16:44:14 GMT

Thanks Chris. I will try that.

I did however ended up trying the below

Aggr(ceil(rank(sum({1<Team=>}Sales)/sum({1<Team=>}Global_Sales))),SalesMan)

and it appears to work for now.