Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
jmvilaplanap
Valued Contributor

Top ten in a pivot table

Hi All,

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

Untitled.png

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

Thanks!

1 Solution

Accepted Solutions

Re: Top ten in a pivot table

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

View solution in original post

8 Replies

Re: Top ten in a pivot table

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

jmvilaplanap
Valued Contributor

Re: Top ten in a pivot table

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

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

View solution in original post

Re: Top ten in a pivot table

Or this as calculated dimension and tick the suppress

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
jmvilaplanap
Valued Contributor

Re: Top ten in a pivot table

It works!

Thank you very much!

jmvilaplanap
Valued Contributor

Re: Top ten in a pivot table

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

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....

jmvilaplanap
Valued Contributor

Re: Top ten in a pivot table

You're an ACE!

Thanks!