topic Re: Display the Top 3 Ranking Value in TextObjects in QlikView

Display the Top 3 Ranking Value in TextObjects

Anonymous — Wed, 12 Aug 2015 04:01:46 GMT

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

];

Re: Display the Top 3 Ranking Value in TextObjects

sunny_talwar — Wed, 12 Aug 2015 04:27:22 GMT

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

Re: Display the Top 3 Ranking Value in TextObjects

jsingh71 — Wed, 12 Aug 2015 04:29:31 GMT

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)

Re: Display the Top 3 Ranking Value in TextObjects

umamaheswarared1 — Thu, 08 Nov 2018 11:26:23 GMT

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".

Re: Display the Top 3 Ranking Value in TextObjects

sunny_talwar — Thu, 08 Nov 2018 23:07:42 GMT

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)

Re: Display the Top 3 Ranking Value in TextObjects

umamaheswarared1 — Fri, 09 Nov 2018 06:45:45 GMT

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)

Thanks,

Umamaheswara Reddy

Re: Display the Top 3 Ranking Value in TextObjects

sunny_talwar — Fri, 09 Nov 2018 11:30:00 GMT

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.

Re: Display the Top 3 Ranking Value in TextObjects

umamaheswarared1 — Fri, 09 Nov 2018 11:32:14 GMT

Sure Sunny,

Thanks for you support