topic Re: FirstSortedValue with same value in QlikView

FirstSortedValue with same value

jduluc12 — Fri, 14 Sep 2018 18:32:04 GMT

Hi,

Is it possible to make FirstSortedValue return values when the sorting field has same values?

For example:

if the data looks like

If I do

FirstSortedValue(Distinct ID, -value) then i get 1

But what if I want to get ID 2?

Can I do something thing like

FirstSortedValue(Distinct ID, -value, 2)

I tried but it does not work.

any suggestions?

Thanks!

neelamsaroha157 — Fri, 14 Sep 2018 18:36:39 GMT

Try this -

FirstSortedValue(Distinct ID, value)

jduluc12 — Fri, 14 Sep 2018 18:38:42 GMT

No it does work.

It brings the ID of the lowest value.

What i need is to get the second ID with highest value.

vishsaggi — Fri, 14 Sep 2018 19:03:40 GMT

Not sure if this is a good way of doing it but you can try below options:

Option1:

=Firstsortedvalue(Aggr(Max(ID), value), Aggr(Max(value, -2), value))

Option2: Tweak in the script like:

Ranking:

LOAD *, Autonumber(ID, value) AS IDRank;

LOAD ID,

value

FROM

(html, codepage is 1252, embedded labels, table is @1);

Add straight table chart with

Dim:

value

Expr:

= Firstsortedvalue(ID, -Aggr(IDRank, ID))