Top ten in a pivot table

[jmvilaplanap]

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!

[sunny_talwar]

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

[jmvilaplanap]

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?

[sunny_talwar · Accepted solution]

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

[Anil_Babu_Samineni]

Or this as calculated dimension and tick the suppress

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

[jmvilaplanap]

It works!

Thank you very much!

[jmvilaplanap]

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

[sunny_talwar]

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]

You're an ACE!

Thanks!