topic Re: Top ten in a pivot table in QlikView

Top ten in a pivot table

jmvilaplanap — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

There is a way to show only the top ten values in a Pivot Table?

I want the top ten (and bottom ten) based in Open Rate

Thanks!

Re: Top ten in a pivot table

sunny_talwar — Wed, 19 Jul 2017 15:50:57 GMT

May be using Rank function on OpenRate to show only those rows which are top 10 and bottom 10

Re: Top ten in a pivot table

jmvilaplanap — Wed, 19 Jul 2017 16:07:27 GMT

Hi stalwar1,

I'm sorry but I don't know how to use this function, I tried a few combinations, but with no result.

My original formula is: if(sum(TotalSent) >= 50,sum(UniqueOpens)/sum(TotalDelivered))

Could you help me?

Re: Top ten in a pivot table

sunny_talwar — Wed, 19 Jul 2017 16:09:45 GMT

May be like this

If(Rank(if(sum(TotalSent) >= 50,sum(UniqueOpens)/sum(TotalDelivered))) < 11, if(sum(TotalSent) >= 50,sum(UniqueOpens)/sum(TotalDelivered)))

And do this for all other expressions

If(Rank(if(sum(TotalSent) >= 50,sum(UniqueOpens)/sum(TotalDelivered))) < 11, Expression2)

If(Rank(if(sum(TotalSent) >= 50,sum(UniqueOpens)/sum(TotalDelivered))) < 11, Expression3)

Note that if condition stays the same, just the expressions changes

Re: Top ten in a pivot table

Anil_Babu_Samineni — Wed, 19 Jul 2017 16:12:17 GMT

Or this as calculated dimension and tick the suppress

Aggr(If(Rank(sum(UniqueOpens)/sum(TotalDelivered), 4) <= 10, ..), .., ..)

Re: Top ten in a pivot table

jmvilaplanap — Wed, 19 Jul 2017 16:12:47 GMT

It works!

Thank you very much!

Re: Top ten in a pivot table

jmvilaplanap — Wed, 19 Jul 2017 16:22:42 GMT

And the bottom ten?

I'm trying something like that:

If(Rank(if(sum(TotalSent) >= 50,sum(UniqueOpens)/sum(TotalDelivered))) > count(total TotalSent) - 10, if(sum(TotalSent) >= 50,sum(UniqueOpens)/sum(TotalDelivered)))

But of course, is not working

Re: Top ten in a pivot table

sunny_talwar — Wed, 19 Jul 2017 16:24:37 GMT

Something like this

If(Rank(-if(sum(TotalSent) >= 50,sum(UniqueOpens)/sum(TotalDelivered))) < 11, if(sum(TotalSent) >= 50,sum(UniqueOpens)/sum(TotalDelivered)))

Note the negative sign after Rank(.... function.... this is to get the rank from the bottom....

Re: Top ten in a pivot table

jmvilaplanap — Wed, 19 Jul 2017 16:27:23 GMT

You're an ACE!

Thanks!