FirstSortedValue() with aggr()?

[MarcinS]

Hi,

I'm having an issue with the FirstSortedValue() function. When I try to get the previous event to calculate the time interval between individual events, I encounter a null value. If I set the offset to 1, the function works, but I need to set the offset to 2 to append the timestamp of event B to event A, and that's the problem. Please help 🙂

FirstSortedValue(DISTINCT aggr( max(Timestamp), ID),Timestamp,1) - on screen and it is working

FirstSortedValue(DISTINCT aggr( max(Timestamp), ID),Timestamp,2) - on second screen, it isn't working but I can see one correct value in sum row hm....

Thanks,

Marcin

[Chirantha]

Hello Marcin,

The FirstSortedValue() function in Qlik Sense returns the value from the expression specified in value that corresponds to the result of sorting the sort_weight argument. The nth value in the sort order can be specified in rank.

However, when you’re using FirstSortedValue() with an offset of 2, it’s important to note that this function will return null if there are not at least two distinct values in the sort_weight argument for the same value argument. This could be the reason why you’re encountering null values.

If you’re trying to calculate the time interval between individual events, you might need to adjust your data model or the way you’re using the function. For example, you could try using the Above() or Below() functions to reference previous or next rows in a table, or consider restructuring your data so that each event is on a separate row.

For more information, please take a look below

FirstSortedValue - script function | Qlik Sense on Windows Help

FirstSortedValue - script function | Qlik Cloud Help