Firstsortedvalue error

Anonymous — Wed, 09 May 2018 07:44:45 GMT

Hi all,

I have requirement to get the TOP 3 products. The data i got is as below:

Department Category Product Runs Type Percent

Department B Category 1 Product A 5 1 20%

Department B Category 1 Product B 6 1 16.67%

Department B Category 1 Product A 4 2 50%

Department B Category 1 Product B 5 1 20%

Expected results is:

Department Category Runs Type Percent TOP1 Product Runs Type Percent TOP2 Product Runs Type Percent

Department B Category 1 20 5 25% Product A 9 3 33.33% Product B 11 2 18.18%

For getting the TOP1 Product i have written the code:

=FirstSortedValue(distinct Product, Num(Aggr(Rank( total sum(type) + sum(Percent),4),Department,Category)),1)

For getting the TOP1 Product Runs i have written the code:

=FirstSortedValue(distinct Run, Num(Aggr(Rank( total sum(type) + sum(Percent),4),Department,Category)),1)

I want to sum the Run for each product.

Please help me.

Re: Firstsortedvalue error

sunny_talwar — Wed, 09 May 2018 11:40:21 GMT

Try this

=FirstSortedValue(Aggr(Sum(Run), Department, Category), Num(Aggr(Rank(TOTAL Sum(type) + Sum(Percent), 4), Department, Category)), 1)

Anonymous — Mon, 14 May 2018 09:40:38 GMT

Thank you! It works.