Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
Can you please help me out?
please check the below llink..