9 Replies Latest reply: Feb 28, 2018 1:35 AM by vidya sagar malla

# 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

• ###### 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

• ###### 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:

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

• ###### Re: Calculate TOP 2 Companies

Use this:

Dimensions:

Year

Company

Expression:

if(

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

sum(Amount),

0

)

• ###### Re: Calculate TOP 2 Companies

Dimension:

Year

Company

Expression:

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

• ###### 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.

• ###### Re: Calculate TOP 2 Companies

Thank you for looking into this.

• ###### Re: Calculate TOP 2 Companies

Dimension:

Year

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

Expression :

Sum(Amount)

• ###### Re: Calculate TOP 2 Companies

Thanks it worked as expected.