How to get Top 5 Sales?

[Anonymous]

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.

[preminqlik]

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...

[Gysbert_Wassenaar · Accepted solution]

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.

[Anonymous]

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

[Anonymous]

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)

[#6]

hi

i think this will help ful  to you

if yes

plz marked this as Answered Question

[preminqlik]

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