Get each store his each site number latest customers quantity

[Itay113]

I have a table of stores, site number, customers quantity and status time

I want to display in a table the latest customers quantity for each store and store site number.

I've tried using firstsortedvalue like this: FirstSortedValue(customersQuantity, -statusTime)

as the measure with store name as dimension and the same measure for site number but I got null.

The table looks like this:

siteName - test1, siteNumber - 1, customersQuantity - 12, status time 2025-06-12T12:45:40PM

siteName - test1, siteNumber - 2, customersQuantity - 1, status time 2025-06-12T12:45:40PM

siteName - test2, siteNumber - 1 customersQuantity - 7, status time 2025-06-12T12:45:40PM

[marcus_sommer]

The cause may be:

"... If more than one resulting value shares the same sort_weight for the specified rank, the function returns NULL ..."

FirstSortedValue - chart function | Qlik Cloud Help

Depending of the data-set and view-requirements it might be possible to get unique values by wrapping the query with n aggr().

More suitable could be to evaluate the essential information already within the data-model, for example to evaluate a min/max timestamp against a self-join aggregation load and/or using interrecord-functions like peek() and previous() to track the order.

[Itay113]

I see.. I can’t use previous or peek because I want it in the ui expression so I can filter it with other fields

How can I achieve this with aggr wrapping it?

[marcus_sommer]

It might go in a direction of:

aggr(FirstSortedValue(customersQuantity, -statusTime), Dim1, Dim2)

or

FirstSortedValue(customersQuantity, aggr(-statusTime, Dim1, Dim2)

or both combined or even further wrapped. Dim1 and Dim2 are just place-holder for the real existing dimensionality.

Such measurements could become quite complex and it might be helpful not to develop such logic within the origin application else within a reduced dummy.

Beside this you shouldn't discard a (pre-) solution within the data-model too quickly because UI solutions are usually much more expensive as within the data-model.