Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Top 20 Cases using rank in expression of pivot

Hi All,

I Need to show top 20 Application (On Amount Basis) as whichever applications having higher 20 amounts should be shown where for cases if amount is repeating like

Case Amount

PA001 1000

PA002 1000

it should should show anyone randomly.

I tried if(rank(sum(Amount))<=20,sum(Amount)) but its not showing proper result as its showing more than 20 cases with

PA001 1000

PA002 1000

repeating, in pivot i have other dimentions in order as

Col2

Channel

Case

Kindly help!!

9 Replies
danieloberbilli
Specialist II
Specialist II

I think rank should be <=20   rank 1,2,...20   so:

if(rank(sum(Amount))<=20,sum(Amount))

Not applicable
Author

Hi,

Sorry, i mistakenly wrote' >=20' its '<=20' only

danieloberbilli
Specialist II
Specialist II

try a new expression with simply

rank(sum(Amount))

there you will see how the ranking looks like for the whole pivot table.

Then you could add another expression to see the effect if you add e.g. 'total'

rank(total sum(Amount))

this might help you  -else try to experiment a bit with 'total <Field>'  depending of what you are aiming for

amit_saini
Master III
Master III

Rohit,

See the attachment.

Thanks,

AS

Not applicable
Author

Hi Daniel,

I m getting such cases ,but i just want to take any one of them , how to do it??

Capture.PNG

Not applicable
Author

Hi Amit,

I have 3 Dimension in pivot table

Col

Channel

Case (For which i did as u suggested in Top and Bottom QVW) (

If(Aggr(Rank(sum(APE_MGMT)) <= 20,Application_Num_Base1),Application_Num_Base1)

)

but its not showing any record ..

Not applicable
Author

first create a input box and create variable then create s traitght table > in dimension table input the variable you made .

what ever no.  you input in inputbox it will display as your top .

ex. input box = 20   then you straith table will display top 20

Not applicable
Author

hello all ,

Please reply u have any suggestions!!

cesaraccardi
Specialist
Specialist

Hi Rohit,

I haven't had a look at Amit's application but I think your expression should be something like this:

If(Aggr(Rank(sum(APE_MGMT)),Application_Num_Base1)<=20,Application_Num_Base1)


I think the check for ranking <=20 should be done after the Aggr() function is applied and not inside the brackets.