Display the Top 3 Ranking Value in TextObjects

[Anonymous]

Hi all,

I have a table like below one, but I want to create 3 Text Objects to list out the top 1, 2 and 3 Category name, does anyone have experience on that, thanks for any ideas.

Cat	Sales
D	100
A	300
B	500
C	1000

Load * Inline [

Product, Cat, Sales

A, A, 100

A1, A, 200

B, B, 100

B1, B, 400

C, C, 1000

D, D, 50

D1, D, 50

];

[sunny_talwar]

Try these:

For Top Category

=FirstSortedValue(Cat, -Aggr(Sum(Sales), Cat), 1) -> Returns C

For 2nd top Category

=FirstSortedValue(Cat, -Aggr(Sum(Sales), Cat), 2) -> Returns B

For 3rd top Category

=FirstSortedValue(Cat, -Aggr(Sum(Sales), Cat), 3) -> Returns A

[jsingh71]

if you want to show top category and sales in Text Object then use below expression in Text Object:

='Top Category and Sales' & chr(10) & 'Cat: '& FirstSortedValue(Cat,-Sales) & ', Sales: ' & Max(Sales)

[umamaheswarared1]

Hi Sunny,

I am looking the same thing but little bit different.

In my scenario i need to show Top 3 even top 1 and top 2 has same sales.

Suppose A:500, B:400, C: 500, D:100

i need to show either "A" or "C" in first text object and "C" or "A" in Second Object then in third Text Object obviously "B".

[sunny_talwar]

Try this

=FirstSortedValue(Cat, -Aggr(RangeSum(Sum(Sales), Rank(Cat)/1E10), Cat), 1)

=FirstSortedValue(Cat, -Aggr(RangeSum(Sum(Sales), Rank(Cat)/1E10), Cat), 2)

=FirstSortedValue(Cat, -Aggr(RangeSum(Sum(Sales), Rank(Cat)/1E10), Cat), 3)

[umamaheswarared1]

Thanks Sunny for your quick response it is working when ever we need rank for single field and group by same field.

But in my case i want to calculate rank  and group by multiple fields.

Please look into below code. I want to show top product group by multiple fields.

=Firstsortedvalue( Product, -Aggr(RangeSum(Sum([Cases]), Rank(Product)/1E10), Product, Channel, [Ending Milestone], [Case Status],Journey1,Journey2,Journey3,Journey4,Journey5,Journey6,Journey7,Journey8,Journey9), 1)

=Firstsortedvalue( Product, -Aggr(RangeSum(Sum([Cases]), Rank(Product)/1E10), Product, Channel, [Ending Milestone], [Case Status],Journey1,Journey2,Journey3,Journey4,Journey5,Journey6,Journey7,Journey8,Journey9), 2)

=Firstsortedvalue( Product, -Aggr(RangeSum(Sum([Cases]), Rank(Product)/1E10), Product, Channel, [Ending Milestone], [Case Status],Journey1,Journey2,Journey3,Journey4,Journey5,Journey6,Journey7,Journey8,Journey9), 3)

Thanks,

Umamaheswara Reddy

[sunny_talwar]

I think it would be best if you can create a new thread and share a sample where we can see a better picture of what you have.

[umamaheswarared1]

Sure Sunny,

Thanks for you support