Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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
Valued Contributor II

Re: Top 20 Cases using rank in expression of pivot

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

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

Not applicable

Re: Top 20 Cases using rank in expression of pivot

Hi,

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

danieloberbilli
Valued Contributor II

Re: Top 20 Cases using rank in expression of pivot

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
Honored Contributor III

Re: Top 20 Cases using rank in expression of pivot

Rohit,

See the attachment.

Thanks,

AS

Not applicable

Re: Top 20 Cases using rank in expression of pivot

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

Re: Top 20 Cases using rank in expression of pivot

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

Re: Top 20 Cases using rank in expression of pivot

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

Re: Top 20 Cases using rank in expression of pivot

hello all ,

Please reply u have any suggestions!!

cesaraccardi
Valued Contributor

Re: Top 20 Cases using rank in expression of pivot

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.