Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
like this?
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?
Please upload your sample fie or QVW.
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
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!
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.
Cheers,
Oleg Troyansky
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
like this?
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!