Qlik Community

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 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

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
Highlighted

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
Highlighted

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

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

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

Highlighted

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
Specialist

It works!

Thank you very much!

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

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
Specialist

You're an ACE!

Thanks!