Calculate TOP 2 Companies

[vidyasagar159]

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

[Anonymous]

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]

Dimension:

Year

Company

Expression:

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

[vidyasagar159]

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

[shilpan]

Use this:

Dimensions:

Year

Company

Expression:

if(

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

sum(Amount),

0

)

[yujiyamane]

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 · Accepted solution]

Dimension:

Year

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

Expression :

Sum(Amount)

[vidyasagar159]

Thanks it worked as expected.

[vidyasagar159]

Thanks for your help!!

[vidyasagar159]

Thank you for looking into this.