FirstSortedValue

[morenoju]

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

[morenoju]

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]

Should be just one.

[sunny_talwar · Accepted solution]

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]

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