Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
vidyasagar159
Contributor

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
pritamb89
Contributor

Re: Calculate TOP 2 Companies

Dimension:

Year

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

Expression :

Sum(Amount)

Capture4.PNG

9 Replies

Re: Calculate TOP 2 Companies

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

xufei123
Valued Contributor

Re: Calculate TOP 2 Companies

Dimension:

Year

Company

Expression:

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

vidyasagar159
Contributor

Re: Calculate TOP 2 Companies

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

shilpan9166
Contributor II

Re: Calculate TOP 2 Companies

Use this:

Dimensions:

Year

Company

Expression:

if(

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

sum(Amount),

0

)

yujiyamane
Contributor II

Re: Calculate TOP 2 Companies

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.

pritamb89
Contributor

Re: Calculate TOP 2 Companies

Dimension:

Year

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

Expression :

Sum(Amount)

Capture4.PNG

vidyasagar159
Contributor

Re: Calculate TOP 2 Companies

Thanks it worked as expected.

vidyasagar159
Contributor

Re: Calculate TOP 2 Companies

Thanks for your help!!

vidyasagar159
Contributor

Re: Calculate TOP 2 Companies

Thank you for looking into this.