

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Calculate TOP 2 Companies
Hi,
With the below table. I want to calculate top 2 companies base on the sum of sales. Can some please help me with the logic.
Year | Company | Amount |
2010 | Apple | 10 |
2010 | Nokia | 20 |
2010 | Samsung | 30 |
2010 | Apple | 100 |
2010 | Nokia | 200 |
2010 | Apple | 500 |
2011 | Nokia | 100 |
2011 | Samsung | 200 |
2012 | Apple | 500 |
2012 | Nokia | 150 |
2014 | Apple | 600 |
2014 | Nokia | 100 |
2014 | Samsung | 150 |
2014 | Apple | 300 |
2014 | Nokia | 200 |
Thanks,
-Vidya
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dimension:
Year
Company : =If(Aggr(VRank(TOTAL Sum(Amount),0,1)<=2,Company,Year),Company)
Expression :
Sum(Amount)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try this as dimension
=aggr ( if ( rank(Sum( Amount)) <=2,Company) , Company )
And this as expression
Sum(Amount)
On the Dimension untick Include Null Values

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dimension:
Year
Company
Expression:
if(rank(sum(Amount))<=2,
sum(Amount)
)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Bill,
Thanks for looking into this. But this expression is not giving me the right result. Maybe I did not share right information.
With the expression, you shared I got the result below.
Result:
But the Output I want to see the TOP 2 sales amount is.
This I have achieved by using Spotfire. But I am facing some challenges while migrating the same to QLIK.
This is for TOP 5:
Thanks,
-Vidya

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Use this:
Dimensions:
Year
Company
Expression:
if(
rank(total aggr(sum(Amount), Year, Company)) <= 2,
sum(Amount),
0
)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
One solution i can think now would be to concatenate year and company field in the load script and use the below condition for all the dimensions and expression
If(Aggr(Rank(Sum(Amount)),CompanyYear)<=2,
xxxx
)
Please find the sample document as attachment.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dimension:
Year
Company : =If(Aggr(VRank(TOTAL Sum(Amount),0,1)<=2,Company,Year),Company)
Expression :
Sum(Amount)


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks it worked as expected.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your help!!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you for looking into this.
