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

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
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