Pivot table with two calculated dimensions - rank()

[#1]

Hi all,

I have a question concerning two calculated dimensions in a pivot table (see file attached).

In ArtikelsGM you can find all sorts of articles.

In the pivot table I only want to show specific articles, as you can see. I managed to do this with if(match()) as a calculated dimension.

My question is: I only want to show the top 3 of the sales (per Name)

So basically, the pivot table should only show me Name9, Name7 and Name4 (in that order)

I tried to use the code below as another calculated dimension, but it does not seem to work.

=if(aggr(rank

(sum(Sales))

,Name)<=3,Name)

Can you guys help me out?

Thank you!

[#2]

hi

try this

rank:

LOAD * INLINE [

    name, sales

    a, 200

    b, 100

    c, 300

    d, 500

    e, 450

    f, 356

];

expression--

if(aggr(rank(sum(sales)),name)<=3,name)

or simply write this expression

aggr(if(rank(sum(sales))<=3,name),name)

then output like this

name	sales	if(aggr(rank(sum(sales)),name)<=3,name)
d	500	d
e	450	e
f	356	f

[#3]

Hi Vishwaranjan,

Thanks for the reply, but I am a bit lost. I am pretty new to Qlikview and I only know expressions and dimensions.

Where should I enter the code written below?

rank:

LOAD * INLINE [

    name, sales

    a, 200

    b, 100

    c, 300

    d, 500

    e, 450

    f, 356

];

And the expressions you have given me, is this an expression or a calculated dimension?

Thank you for your help!

Z

[Gysbert_Wassenaar · Accepted solution]

Try this as calculated dimension:

=aggr( if(rank(aggr(sum({<ArtikelsGM={'42110 GIPS ZAKGOED','42210 GIPS SILO','42220 GIPS ZAK M'}>}Sales),Name))<=3,Name),Name)

The part {<ArtikelsGM={'42110 GIPS ZAKGOED','42210 GIPS SILO','42220 GIPS ZAK M'}>} is necessary because you 'select' these values in your other calculated dimension.

You could also use sum({<ArtikelsGM={'42110 GIPS ZAKGOED','42210 GIPS SILO','42220 GIPS ZAK M'}>} Sales) as expression and use ArtikelsGM as dimension instead of the calculated dimension. That's a better way to achieve the same result.

See attached qvw.

[#5]

hi

firstly go to script--

then write this code

rank:

LOAD * INLINE [

    name, sales

    a, 200

    b, 100

    c, 300

    d, 500

    e, 450

    f, 356

];

now on dashboard, right clik, and select new object--->

select pivote table.

take

dimension----  name , sales

expression---- 
if(aggr(rank(sum(sales)),name)<=3,name)

[#6]

@ vishwaranjan: Thank you for your help, I tried Gysbert's answer and it worked, so I perfer to not touch the script. But I tried yours in a test-file and it works as well. So many thanks!

@ Gysbert: thank you for your reply, it works like a charm! Just one small qestion though:

Instead of "Sales", the formule to calculate my sales is a bit more complex:

sum({$<Jaar={$(=only(Jaar))}>} OmzetBedrag)

How should I enter this in your codes? I copied the bold part to put it at the Sales part, but it did not work. How should I squeeze it in?

In calculated dimensions I now have;

=aggr( if(rank(aggr(sum({<ArtikelsGM={'42110 GIPS ZAKGOED','42210 GIPS SILO','42220 GIPS ZAK M'}>}Sales),Name))<=3,Name),Name)

and in expressions I use;

=sum({<ArtikelsGM={'42110 GIPS ZAKGOED','42210 GIPS SILO','42220 GIPS ZAK M'}>}Sales)

So both times, the sales part should become

sum({$<Jaar={$(=only(Jaar))}>} OmzetBedrag)

Thanks a lot!

Zipke

[#7]

Alright, I have found the solution.

Thanks for the help!

Cheers,

Zipke