Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

How to leave ranking unchanged after selection using Count Total Distinct minus Rank Avg

Hi all. I have tried to search for similar topics but cannot find set analysis to achieve my specific requirement. Given the following formula how do I ensure when I select one company, the ranking remains unchanged, currently when I select a company it changes the rank to 1:

=(Count({<[Country] = {'Country 2'}>} Total Distinct [Company]))+1-(Rank(Avg({1}{<[Country] = {'Country 2'}>}[Avg % Transaction Cost]/100)))

 

I have attached a sample of the data and qvf file (although my actual dataset is much larger, with many more countries and many more companies)

Labels (2)
1 Solution

Accepted Solutions
Highlighted

Is this what you are looking to get?

Annotation.png

Basically change your dimension to be just Company and then your expression to be

=(Count({1<[Country] = {'Country 2'}>} Total Distinct [Company]))+1-(Rank(Avg({1<[Country] = {'Country 2'}>}[Avg % Transaction Cost]/100))) * Avg(1)

View solution in original post

3 Replies
Highlighted

Is this what you are looking to get?

Annotation.png

Basically change your dimension to be just Company and then your expression to be

=(Count({1<[Country] = {'Country 2'}>} Total Distinct [Company]))+1-(Rank(Avg({1<[Country] = {'Country 2'}>}[Avg % Transaction Cost]/100))) * Avg(1)

View solution in original post

Highlighted
Contributor II
Contributor II

Thank you so much @sunny_talwar  I see you are a guru in the Qlik community, a lot of your responses on other threads have also helped me. I changed dimension to Company only and used your formula and it's now showing me the correct rank 🙂

Highlighted
MVP
MVP

You probably missed the last section of expression and that is the trick which is actually reducing the values in chart.

....* Avg(1)