topic Aggr(Rank function help needed:Top 10 "for each Brand" wanted Not Total top 10 in Pivot Table in QlikView

Aggr(Rank function help needed:Top 10 "for each Brand" wanted Not Total top 10 in Pivot Table

Thu, 29 Sep 2011 16:29:25 GMT

Two Brands:

Coke

Pepsi

Each Brand have multiple flavors

I have the below aggr(rank formula that gives me Total Top 10 Customer sales

but I want Top Ten Customer Sales for Each brand, "Not Total combined top 10"

=IF(

aggr(

rank(total

aggr(sum([2011 Sales]),Flavor)

,0,1)

,Flavor)

<=10,

aggr(

rank(total

aggr(sum([2011 Sales]),Flavor)

,0,1)

,Flavor))

If i select a Brand Coke or Pepsi then my Top 10 pivot table recalcuates the Top 10 for the Brand selection (which is what i want to show by default and not have to manually select a brand for a Top 10 by brand to be calculated)..

I am trying to display Top 10 for each brand by default, and not have to manually select brand for the Top10 to recalculate.

The attached example is version 10, once opened you will have to turn Web view off,since it is set up to open in webview upon opening.

Once opened, Click View, Turn On/Off Webview

attached is an example:

Re: Aggr(Rank function help needed:Top 10 "for each Brand" wanted Not Total top 10 in Pivot Table

Miguel_Angel_Baeyens — Thu, 29 Sep 2011 17:26:02 GMT

Hi Joseph,

Try the following calculated dimension instead, and check the "Suppress When Value Is Null" to avoid results that are not in the top 10 showed with null "No"

=If(Aggr(Rank(Aggr(Sum([2011 Sales]), Brand, Flavor), 3, 1), Brand, Flavor) <= 10,
Aggr(Rank(Aggr(Sum([2011 Sales]), Brand, Flavor), 3, 1), Brand, Flavor)
)

Hope that was what you were looking for.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Aggr(Rank function help needed:Top 10 "for each Brand" wanted Not Total top 10 in Pivot Table

Thu, 29 Sep 2011 17:44:11 GMT

Miguel,

I knew i was on the right track but am very new to Aggr and Rank funtions. Thank you for your help.

This worked perfectly. I know that Top customers, sales, etc come up a lot with in pivot tables so hopefully this can help someone else too.

Kind regards,

Joseph

Aggr(Rank function help needed:Top 10 "for each Brand" wanted Not Total top 10 in Pivot Table

Fri, 20 Jan 2012 23:59:03 GMT

Miguel this worked great, for what does the 3 represent and what does the 1 represent?

Aggr(Rank function help needed:Top 10 "for each Brand" wanted Not Total top 10 in Pivot Table

Sat, 21 Jan 2012 08:28:59 GMT

It's parameters from the rank function to treat the equal item.

How to calculate all the equal ?

How to display ?

Look at the help.

Re: Aggr(Rank function help needed:Top 10 "for each Brand" wanted Not Total top 10 in Pivot Table

Anonymous — Fri, 02 Mar 2018 10:04:56 GMT

Thanks so much for this. It has helped me so much.

I know this post is old, so not sure if you can still help me.

I want to use set analysis to exclude selections, but it doesn't seem to work in the calculated dimension for the rank.

I just added the {1} but it only brings back whatever my selections are. Is there something else I should be doing?

=If(Aggr(Rank(Aggr(Sum({1}[2011 Sales]), Brand, Flavor), 3, 1), Brand, Flavor) <= 10,

Aggr(Rank(Aggr(Sum({1}[2011 Sales]), Brand, Flavor), 3, 1), Brand, Flavor)

)