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

[sunny_talwar]

Why do you need FirstSortedValue() function here... may be you need Max()?

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

[morenoju]

Actually Min, yes, that would work. But I have to display next to it the second element that meet the conditions, and then the third, and so on. That's why I need the FirstSortedValue() function.

[agigliotti]

maybe there are more event_duration sharing the same value.

[sunny_talwar]

Not sure I follow this... can you share an example?

[morenoju]

Thanks Andrea, but I have them all displayed on a table and it's not the case.

[agigliotti]

could you describe your table dimensions and measures ?

[morenoju]

Sorry Sunny, my bad. You're right about the "Max". But the reason to use FirstSortedValue is that I want to show more than one event.

I have several "gauge" elements on my dashboard, using the following expressions:

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

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

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

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

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

The objective is to show the 5 events with the longest duration, but that don't exceed the limit (200).

Thanks,

Juan

[morenoju]

I've used the following columns:

event_code, event_duration, event_status_id, is_planned_event

Then I've filtered by event_status_id=22 and is_planned_event=0. Finally, I've ordered the events by duration to verify there are some of them shorter than 200.

For the gauge, I've also removed the data handling condition I had to be sure it doesn't affect the display of the data.

Thanks.

[sunny_talwar]

You can do the same with Max also

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

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

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

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

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