Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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:

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

5 Replies
Miguel_Angel_Baeyens

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

View solution in original post

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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.

JJ

lyndluyt
Contributor III
Contributor III

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)