8 Replies Latest reply: Feb 26, 2014 2:00 AM by Oleg Troyansky

# Set Analysis + Top5

Hi all!

I would like to create a Pivot Chart which shows me, independently from my current selection, the Top 5 Productlines within a certain Productgroup plus all the Customers who bought one of the Top Five Products.

It should look like this:

Top 5 ProductlinesCustomerQuantity
Line AA
B
C
D
123145
32462346
23642364
346236
Line BD
R
U
P
H
123525
234623
346236
2346
34
Line CS
J
E
1231235
46794697
2345
Line DE
O
W
137872
83523
1471234
Line EK
Q
W
U
171246
82546
671345
13456

My Basic Formula for Set Analysis looks like this:

sum({\$<Productgroup = {Connectors}>} Quantity) - unfortunately this formula doesn't evaluate the top five Connectors. The question therefore is, what to do in order to have only the five Productlines with the most quantity sold?

I am looking forward to any suggestion!

Sebastian

• ###### Re: Set Analysis + Top5

The function aggr can be used on the product lines and sorted in descending order to get the top product lines on the top. Then we can limit the table to display only 5 of those using dimension limits or table limits. However, can you share a sample QVW file so that this can be worked out?

• ###### Re: Set Analysis + Top5

Sebastian,

Try this:

=aggr( if(rank(sum(Sales))<=5, ProductLine),  ProductLine)

Adapt to your model for exact names. It will be a calculated dimension.

Fabrice

• ###### Re: Set Analysis + Top5

Maybe something like this in expression:

sum({\$<Productlines={"=rank(sum({<Productgroup = {'Connectors'}>}Quantity))<=5"}>} Quantity)

or maybe you can change you dimension to a calculated dimension like:

aggr(only({\$<Productlines={"=rank(sum({<Productgroup = {'Connectors'}>}Quantity))<=5"}>}Productlines), Productlines)

Hope this helps!

• ###### Re: Set Analysis + Top5

I posted a blog article, describing just that:

http://www.naturalsynergies.com/q-tip-5-show-top-performers-common-but-not-too-trivial/

Also, come and learn Advanced Set Analysis at the Masters Summit for QlikView - April 1-3 in Chicago.

Cheers,

Oleg Troyansky

• ###### Re: Re: Set Analysis + Top5

Good morning everyone,

I tried different versions of your kind suggestions, unfortunately though i couldn't make it working. Attached you will find a .qvd. The only thing missing is the limitation to the 5 values with the most quantity.

regards

Sebastian

• ###### Re: Re: Re: Set Analysis + Top5

like this?

• ###### Re: Re: Set Analysis + Top5

That works like a charm Manish, thank you very much! In the meantime i tried jerem1234's version again and it worked too. So we have two solutions, both perfectly working.

Thank you very much guys!