topic Re: help in QlikView

help

Thu, 02 Jul 2015 07:38:30 GMT

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

Re: help

ankit777 — Thu, 02 Jul 2015 07:42:02 GMT

you can use Max(sales,2)

Re: help

Chanty4u — Thu, 02 Jul 2015 07:50:33 GMT

u can try

Max(sales)-1

Chanty

Re: help

Thu, 02 Jul 2015 07:54:01 GMT

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

Re: help

MK_QSL — Thu, 02 Jul 2015 08:15:34 GMT

2nd Max Sales

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

Fields Related to 2nd Max Sales

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

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

Re: help

jagan — Thu, 02 Jul 2015 08:28:32 GMT

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.

Re: help

stephenedberkg — Thu, 02 Jul 2015 08:29:14 GMT

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

Re: help

ankit777 — Thu, 02 Jul 2015 08:48:46 GMT

use

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