Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gabriele_qlik
Contributor III
Contributor III

Problem with FirstSortedValue()

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

1 Solution

Accepted Solutions
sunny_talwar

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))

View solution in original post

5 Replies
sunny_talwar

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

Thank youuuu

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