Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have sample data as shown below.
I want to create a straight table with country as dimension. I want to show sum sales for each country, but only the top 3 sales.
Country Sale
A 5
A 2
A 3
A 7
A 1
B 9
B 2
B 3
B 12
B 4
C 15
C 2
C 1
C 7
C 8
Desired Output
A 15
B 25
C 30
How to achieve this?
Hi Ankit,
You can use Rank function.
Syntax:
=rank(sum(Investment),1,1)
AND
=aggr(if(Rank(sum(Investment))<=3,Investment),Investment) (For calculating Top 3)
AND
=if(rank(sum(Investment),1,1)=1,'# 1')
Thanks,
AS
Which version of Qlik do you have?
There is an option (Dimension Limit) in the latest version that easily allow you to obtain what you need ...
Hi try like this:
IF(Aggr(Rank(sum(Sale)),Country)<=3,sum(Sales))
Hope this will help you
Check attachments
Thanks,
AS
If you interested in the distinct Top 3 values, try this in a chart with dimension Country:
=Rangesum( Max(Sale), Max(Sale,2),Max(Sale,3) )
none of these seem to work for me..
Hey, I don't get the desired result.. it sums up all the values.
Hey,
That works perfectly. But is there an alternative using Rank function?(just to make it dynamic like if its top 10)
Maybe like this:
=sum(aggr( if(rank(Sale)<=3,Sale),Country,Sale))