topic Re: show top 3 values per first dimension in QlikView

show top 3 values per first dimension

chriscools — Mon, 29 Oct 2012 21:35:18 GMT

hello,

i'm creating a pivot table with 2 dimensions:

-sales family

-art code

as a value i have the sum of sales of the selected month.

now i would like to show per sales family only the top 3 articles with it's sales, is this possible?

thanx!

grtz,

chris

Re: show top 3 values per first dimension

MayilVahanan — Mon, 29 Oct 2012 21:44:56 GMT

Try like this

Expression tab:

=If(Rank(Sum(sales)) <=3, Sum(Sales))

Then suppress null values in dimesion tab

Hope it helps

Re: show top 3 values per first dimension

chriscools — Mon, 29 Oct 2012 22:07:57 GMT

Hello,

thank you for your reply!

and how should i mingle it with the formula i had in mind with set analysis?

Sum ({<Shop= {'Pomax Store Wijnegem'}, Year = {$(= Max(Year))}>}LineSalesAmount)

thanx!

chris

Re: show top 3 values per first dimension

MayilVahanan — Mon, 29 Oct 2012 22:11:49 GMT

Try like this

=if(rank(

Sum ({<Shop= {'Pomax Store Wijnegem'}, Year = {$(= Max(Year))}>}LineSalesAmount) ) < = 3,

Sum ({<Shop= {'Pomax Store Wijnegem'}, Year = {$(= Max(Year))}>}LineSalesAmount) )

Hope it helps

Re: show top 3 values per first dimension

chriscools — Wed, 31 Oct 2012 21:26:09 GMT

Hello,

thank you for your help, i got the formula to work!

it only calculates the top 3 articles for each sales family.

but i can not let it show only the top 3 ones...

if i select "suppres null values" under the dimension it doesn't change...

could you perhaps have a look at it?

thank you very much!

grtz,

chris

Re: show top 3 values per first dimension

Thu, 01 Nov 2012 11:54:44 GMT

use the dimension limits tab

to filter the top 3 values

Re: show top 3 values per first dimension

chriscools — Thu, 01 Nov 2012 15:29:39 GMT

Hey,

that doesn't work...

that way it gives me the top 3 values of the dimensions, but i wan't for each dimension only the top 3 calculated values are shown.

and with the expression provided by Mayil it only get value for the top 3 articles and the rest gets a null value.

but then i can not get to hide these null-values...

chris

Re: show top 3 values per first dimension

MayilVahanan — Thu, 01 Nov 2012 16:45:13 GMT

Are you expect like the attached file?

Re: show top 3 values per first dimension

chriscools — Thu, 01 Nov 2012 17:02:22 GMT

indeed, that's exactly what i want! 🙂

could you please explain me what i was doing wrong?

thanx!!!

Re: show top 3 values per first dimension

MayilVahanan — Thu, 01 Nov 2012 17:11:49 GMT

for ex:

Aggr(Rank(Sum(Sales)),Product,SkuCode)

it calculate the rank for each product and skucode in that product..

if(Aggr(Rank(Sum(Sales)),Product,SkuCode) <= 3, Sum(Sales)) //not overall rank..

rank(sum(sales)) calculate the rank for overall dimension.. so only it shows first three ranking values n for others it display null..

Re: show top 3 values per first dimension

chriscools — Thu, 01 Nov 2012 17:15:19 GMT

thanx!!