Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

help

Hi All,

How to find 2nd highest sales.

Using 1st sorted value we can extract related fields of min & max.

But at this requirement I need to only 2nd highest sales.

How can we achieve this??

PFB of related info for your reference .

Plz help me out

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

2nd Max Sales

=Max(Aggr(SUM(sales),id,name),2)

Fields Related to 2nd Max Sales

=FirstSortedValue(name, -Aggr(SUM(sales),id,name),2)

or

=FirstSortedValue(Distinct name, -Aggr(SUM(sales),id,name),2)

View solution in original post

7 Replies
ankit777
Specialist
Specialist

Hi

you can use Max(sales,2)

Chanty4u
MVP
MVP

hi

u can try

Max(sales)-1

Chanty

Not applicable
Author

Yes I got 2nd highest sale but hot extract related field??

I tried in text object but it didn't work.

=FirstSortedValue(name, -Max(sales,2))

Kindly look at this

MK_QSL
MVP
MVP

2nd Max Sales

=Max(Aggr(SUM(sales),id,name),2)

Fields Related to 2nd Max Sales

=FirstSortedValue(name, -Aggr(SUM(sales),id,name),2)

or

=FirstSortedValue(Distinct name, -Aggr(SUM(sales),id,name),2)

jagan
Partner - Champion III
Partner - Champion III

Hi,

Try using Rank() and Aggr() like below

For finding the Salesman name with Second  Max sales

=Concat(Aggr(If(Rank(Sum(Sales)) = 2, Salesman), Salesman), ', ')

If you want to find the sales value then

=Max(Aggr(If(Rank(Sum(Sales)) = 2, Sum(Sales)), Salesman))

Hope this helps you.

Regards,

Jagan.

stephenedberkg
Creator III
Creator III

Dearbabiqlik

=sum( {$<Year={$(=Max(Year))},MonthID={$(=Max(MonthID))},

[sales Man]={$(=only(if(aggr(Rank( sum (SALES)),[sales Man])=1,[sales Man])))}>} SALES)

you want max year and month sales mean use this or delete the max year and month Function in this expression

ankit777
Specialist
Specialist

use

FirstSortedValue(name, -Aggr(SUM(sales),name),2)