Max()/Rank() function question

Thu, 01 Aug 2013 14:57:54 GMT

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.

Re: Max()/Rank() function question

swuehl — Thu, 01 Aug 2013 18:57:17 GMT

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.

Re: Max()/Rank() function question

suvechha_b — Thu, 24 Oct 2013 07:56:51 GMT

Can you please help me out?

please check the below llink..

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

topic Max()/Rank() function question in QlikView

Max()/Rank() function question

Re: Max()/Rank() function question

Re: Max()/Rank() function question