topic Re: Set Analysis + Top5 in QlikView

Set Analysis + Top5

Mon, 24 Feb 2014 15:43:04 GMT

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

Re: Set Analysis + Top5

Mon, 24 Feb 2014 15:51:13 GMT

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

MK_QSL — Mon, 24 Feb 2014 15:52:10 GMT

Please upload your sample fie or QVW.

Re: Set Analysis + Top5

Mon, 24 Feb 2014 16:02:27 GMT

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

jerem1234 — Mon, 24 Feb 2014 16:09:45 GMT

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

Oleg_Troyansky — Mon, 24 Feb 2014 16:25:56 GMT

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

Re: Re: Set Analysis + Top5

Tue, 25 Feb 2014 07:53:19 GMT

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

MK_QSL — Tue, 25 Feb 2014 08:14:44 GMT

like this?

Re: Re: Set Analysis + Top5

Tue, 25 Feb 2014 08:40:16 GMT

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!