Set Analysis + Top5

[#1]

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 Productlines	Customer	Quantity
Line A	A B C D	123145 32462346 23642364 346236
Line B	D R U P H	123525 234623 346236 2346 34
Line C	S J E	1231235 46794697 2345
Line D	E O W	137872 83523 1471234
Line E	K 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

[#2]

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?

[MK_QSL]

Please upload your sample fie or QVW.

[#4]

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

[jerem1234]

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!

[Oleg_Troyansky]

I posted a blog article, describing just that:

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

Please check it out.

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

www.masterssummit.com

Cheers,

Oleg Troyansky

[#7]

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

[MK_QSL · Accepted solution]

like this?

[#9]

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!