topic Re: How to get Top 5 Sales? in QlikView

How to get Top 5 Sales?

Anonymous — Sat, 05 Oct 2013 05:15:46 GMT

Hi,

I am new to QlikView and trying to evaluate this simple scenario where I need to display top 5 sales by Product from the given data in a Bar Chart. (Please refer to the attached excel file Product_sales.xlsx).

I used two dimensions:

1. Product

2. =if(aggr(rank(sum(Sales), product)<=5, product)

Later in the expressions I simply used sum(Sales). But the same isn't working. Please assist.

Thank You.

Re: How to get Top 5 Sales?

preminqlik — Sat, 05 Oct 2013 06:30:27 GMT

open chart select product as dimension
open dimension limits tab >> highlet the limits selection box, >>> show largest 5 values
open expression tab add your expression
go to sort tab ...sort the dimension with same expression as you entered in the expression tab.....with descending order...

Re: How to get Top 5 Sales?

Gysbert_Wassenaar — Sat, 05 Oct 2013 07:06:13 GMT

If you want to use a calculated dimension instead of Dimension Limits then you need to use only the calculated dimension. So remove the product dimension. The expression for the calculated dimension should be =if(aggr(rank(sum(sales)),product)<=5,product). You'll also want to enable the option Suppress When Value Is Null for the calculated dimension.

Re: How to get Top 5 Sales?

Anonymous — Sat, 05 Oct 2013 07:45:56 GMT

Hi..Thanks for the solution. it's working.

Re: How to get Top 5 Sales?

Anonymous — Sat, 05 Oct 2013 07:58:48 GMT

Thanks Prem..working by setting dimension limits also. I also got a dynamic solution using calculated dimension. =if(aggr(rank(sum(sales)),product)<=5,product)

Re: How to get Top 5 Sales?

Sat, 05 Oct 2013 10:52:59 GMT

i think this will help ful to you

if yes

plz marked this as Answered Question

Re: How to get Top 5 Sales?

preminqlik — Fri, 18 Oct 2013 11:24:14 GMT

as per dimension limits the following additional result also works,

as per your soln,once check by taking variable vTop and make scale with this variable and vary it

=if(aggr(rank(sum(sales)),product)<=$(vTop),product)

(when using dimension limits ,limit the largest value as =$(vTop))

regards

premhas