# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for
Did you mean:
Highlighted Specialist

## Top ten in a pivot table

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!

1 Solution

Accepted Solutions
Highlighted MVP

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

8 Replies
Highlighted MVP

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

Highlighted Specialist

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?

Highlighted MVP

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

Highlighted MVP

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)
Highlighted Specialist

It works!

Thank you very much!

Highlighted Specialist

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 Highlighted MVP

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

Highlighted Specialist

You're an ACE!

Thanks! 