Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vidyasagar159
Creator II
Creator II

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.

YearCompanyAmount
2010Apple10
2010Nokia20
2010Samsung30
2010Apple100
2010Nokia200
2010Apple500
2011Nokia100
2011Samsung200
2012Apple500
2012Nokia150
2014Apple600
2014Nokia100
2014Samsung150
2014Apple300
2014Nokia200

Thanks,

-Vidya

1 Solution

Accepted Solutions
jerryyang756
Creator
Creator

Dimension:

Year

Company : =If(Aggr(VRank(TOTAL Sum(Amount),0,1)<=2,Company,Year),Company)

Expression :

Sum(Amount)

Capture4.PNG

View solution in original post

9 Replies
Anonymous
Not applicable

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

NZFei
Partner - Specialist
Partner - Specialist

Dimension:

Year

Company

Expression:

if(rank(sum(Amount))<=2,
sum(Amount)
)

vidyasagar159
Creator II
Creator II
Author

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:


top N.PNG

But the Output I want to see the TOP 2 sales amount is.


Top 2.PNG

This I have achieved by using Spotfire. But I am facing some challenges while migrating the same to QLIK.

This is for TOP 5:

TOP 5.PNG

  Thanks,

-Vidya

shilpan
Partner Ambassador
Partner Ambassador

Use this:

Dimensions:

Year

Company

Expression:

if(

rank(total  aggr(sum(Amount), Year, Company))  <= 2,

sum(Amount),

0

)

yujiyamane
Creator II
Creator II

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.

jerryyang756
Creator
Creator

Dimension:

Year

Company : =If(Aggr(VRank(TOTAL Sum(Amount),0,1)<=2,Company,Year),Company)

Expression :

Sum(Amount)

Capture4.PNG

vidyasagar159
Creator II
Creator II
Author

Thanks it worked as expected.

vidyasagar159
Creator II
Creator II
Author

Thanks for your help!!

vidyasagar159
Creator II
Creator II
Author

Thank you for looking into this.