Problem with FirstSortedValue()

[gabriele_qlik]

Hi,

I have a dataset like this

YEAR     SALES

2012          100

2013          150

2014          200

2015          140

2016          80

2017          300

2018          300

If I use

FirstSortedValue(YEAR, - SALES)

Qlik visualize nothing because there are two year with a maximum of sales (2017 and 2018). In this case if I want choose 2018 or 2017 what I have to do?

I need a script that I can use for other analysis then in my script I can't write 2017 or 2018 because in the next analysis the maximum sales can change.

Thank you

[sunny_talwar · Accepted solution]

If you are okay with seeing any of the two years, you can add DISTINCT

FirstSortedValue(DISTINCT YEAR, - SALES)

If you wish to see the bigger year... then this

FirstSortedValue(YEAR, -(SALES + Year/1E10))

For smaller year...

FirstSortedValue(YEAR, -(SALES - Year/1E10))

[gabriele_qlik]

Thank you

But for example, If I haven't field YEAR but I have field TOWN or ADDRESS_OF_FABRIC and I have two town or two address with maximum sales how can I do?

Thank you too much

[sunny_talwar]

Which of the two would you want to show? If it is random, you can use this

FirstSortedValue(DISTINCT ADDRESS_OF_FABRIC, - SALES)

or you want to show both?

Concat(DISTINCT Aggr(If(SALES = Max(TOTAL <ADDRESS_OF_FABRIC> SALES), ADDRESS_OF_FABRIC), ADDRESS_OF_FABRIC, SALES))

[gabriele_qlik]

I love you... ahahahahahah

Thank you very much. In any analysis I need to show both, in others depends (anytime one town than another).

[gabriele_qlik]

Thank youuuu

In any analysis I need to show both, in others depends (anytime one town than another).