Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Display the Top 3 Ranking Value in TextObjects

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
D100
A300
B500
C1000

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

];

7 Replies
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
Partner - Specialist
Partner - Specialist

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

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

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

Sure Sunny,

Thanks for you support