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: 
morenoju
Partner - Specialist
Partner - Specialist

FirstSortedValue

Hi all,

I have a database with events, each of them with certain properties (status, etc.) and duration.

I want to display the event that matches some properties and has the longest duration without exceeding a limit. I've written the following expression:

FirstSortedValue({1<is_planned_event={"0"},event_status_id={"22"}, event_duration={"<200"}>} event_duration,-event_duration, 1)

Nothing is shown. Why?

On a table, I've been able to display an event with is_planned_event={"0"},event_status_id={"22"} and event_duration=17. So there's definitely something in my DB that should be shown. Do you see any mistake in the expression?


Thanks,


Juan

15 Replies
morenoju
Partner - Specialist
Partner - Specialist
Author

Wow, I didn't know that. Thanks Sunny.

One question though. I have to show in the footnote of the gauge the description of the event whose duration is longer but doesn't exceed the limit. I tried to do:

FirstSortedValue({1<is_planned_event={"0"},event_status_id={"22"}, event_duration={"<200"}>} event_description,-event_duration, 1)

Is this something that can be achieved with Max too?

Thanks

sunny_talwar

Is there only one event_description possible? or are there multiple event_duration which have the max value and hence need to be concatenated?

morenoju
Partner - Specialist
Partner - Specialist
Author

Should be just one.

sunny_talwar

Try adding DISTINCT and see what happens

FirstSortedValue(DISTINCT {1<is_planned_event={"0"},event_status_id={"22"}, event_duration={"<200"}>} event_description,-event_duration, 1)

morenoju
Partner - Specialist
Partner - Specialist
Author

It works!

No idea why... but it does. Thank you Sunny! How did you come up with that?

sunny_talwar

The problem is that FirstSortedValue have trouble when there are multiple values to choose from.... not sure, but may be you have multiple event_duration with the same event_description... but by using DISTINCT... it is able to display the result...