topic Re: Top 10 Sales in QlikView

Top 10 Sales

Anonymous — Thu, 03 Oct 2013 14:16:05 GMT

I'm trying to calculate the sum sales of top 10 Clients in a chart by using:

=if(Aggr(Rank(sum({<Fiscal.WeekNum={$(vCurrWeek)}, Fiscal.Year={$(vCurrYear)}>} Sales)/1000), Sales)<=10, sum({<Fiscal.WeekNum={$(vCurrWeek)}, Fiscal.Year={$(vCurrYear)}>} Sales)/1000)

but not getting any result.

Not sure where I'm going wrong.

Re: Top 10 Sales

Anonymous — Thu, 03 Oct 2013 14:21:33 GMT

Hi Arsalan malik,

in qlikview 11 we have a dimension limits there u can get the result i think.

Re: Top 10 Sales

Anonymous — Thu, 03 Oct 2013 14:29:28 GMT

understand that feature Rajesh but I need to show the sum in a row of Top 10.

Re: Top 10 Sales

Thu, 03 Oct 2013 14:48:36 GMT

Hi,

I think you should do

sum( if(rank(...) <=10, Sales)

(other way round)

Fabrice

Re: Top 10 Sales

MK_QSL — Thu, 03 Oct 2013 15:34:07 GMT

1. Use Client Name as Dimension and tick Supress When Value is Null

2. Use below formula in expression

=if(Rank(AGGR(sum({$<Fiscal.WeekNum={$(vCurrWeek)}, Fiscal.Year={$(vCurrYear)}>} Sales)/1000), Client_Name)<=10,

Aggr(sum({$<Fiscal.WeekNum={$(vCurrWeek)}, Fiscal.Year={$(vCurrYear)}>} Sales)/1000),Client_Name)

Re: Top 10 Sales

abeyphilip — Thu, 03 Oct 2013 15:37:15 GMT

Hi,

Shouldn't the aggregation be done based on the client (and not on the sales)?

Regards,

Abey

Re: Top 10 Sales

shree909 — Thu, 03 Oct 2013 15:40:41 GMT

Hi ,

If u are using the version as 11.

Write u r expression as normal then when u goto the presentaion tab of the straight table check the option sort indicator.

once u get all the values of the expression sort it by max to min or vice versa.(by clicking on the small arrow )

After that goto the dimension limits check to select the first 10 values.

then ur good to go..

Hope this is helpful..

Re: Top 10 Sales

Thu, 03 Oct 2013 15:51:56 GMT

Try this

Calculated Dimension:=if(Aggr(Rank(sum({<Fiscal.WeekNum={$(vCurrWeek)}, Fiscal.Year={$(vCurrYear)}>} Sales)/1000), Client)<=10, Client)

Expression: =sum({<Fiscal.WeekNum={$(vCurrWeek)}, Fiscal.Year={$(vCurrYear)}>} Sales)/1000