Display each name and latest status by time

[Itay113]

I have a table with these columns: name, category, status, dtime. I want to get latest status for each name from the same category even if I filter specific dtime and latest name’s status dtime is before filtered dtime. For example name - jack, category - worker, status - available, dtime - 2025-01-19 20:00:00, name - jack, category - worker, status - unavailable , dtime - 2025-01-19 21:00:00, name - mike, category - worker, status - available, dtime - 2025-01-19 20:00:00. So if I filter to 2025-01-19 21:00:00 I want to get name - jack, category - worker, status - unavailable , dtime - 2025-01-19 21:00:00, name - mike, category - worker, status - available, dtime - 2025-01-19 20:00:00

[JandreKillianRIC]

Hi @Itay113 

To achieve this in Qlik Sense, you can use a combination of Aggr() and FirstSortedValue() functions to get the latest status for each name within the same category, even when filtering by a specific dtime. Here's how you can do it:

FirstSortedValue(

    {<dtime = {"<=$(vFilteredDTime)"}>} status,

    -Aggr(Max({<dtime = {"<=$(vFilteredDTime)"}>} dtime), name, category)

)

Regards - Jandre

[Itay113]

How can I instead of using variable value, use field value selected?

[JandreKillianRIC · Accepted solution]

Hi @Itay113 

FirstSortedValue(

    {<dtime = {"<=$(=dtime)"}>} status,

    -Aggr(Max({<dtime = {"<=$(=dtime)"}>} dtime), name, category)

)

[Itay113]

Works, thanks 😁