Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Max()/Rank() function question

I'm trying to find the system usage of:

1.) top user

2.) sum of the top 3 users, and

3.) sum of the top 10 users

To do this I'm utilizing a max(aggr()) funciton, and to find the next highest after the top user, I'm using max(aggr(sum(Usage), User), 2), max(aggr(sum(Usage), User), 3), etc. [ max(variable, x) - where "x" is the x highest variable ].

This works when the usage by each user is different, but let's say we have this:

User         Sum of Usage

UserA              25

UserB              10

UserC               7

UserD              10

UserE              10

This will return:

Max: 25 (UserA)

Top 3: 25+10+7 = 42       (UserA + [UserB or UserC or UserE] + User D)

What I want is for "Top 3" to return 25 + 10 + 10 (UserA + UserB + UserD; essentially finding ANY of the top 3 usage sums, even if the usage is repeated in some users). Does Qlikview have a function to simply pick the top 3 values, and if any values are repeated, simply pick any one of the values that add up to the top x?

I'm not sure if that's clear; let me know if you need restating.

2 Replies
swuehl
MVP
MVP

I think you already told yourself the answer, it's the rank() function.

You can use something like

=sum({<User = {"=rank(sum(Usage),4,0) <=3"}>} Usage)

Please check attached.

suvechha_b
Creator III
Creator III

Can you please help me out?

please check the below llink..

http://community.qlik.com/message/411018#411018