topic Re: Display each name and latest status by time in App Development

Display each name and latest status by time

Itay113 — Sun, 19 Jan 2025 18:58:07 GMT

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

Re: Display each name and latest status by time

JandreKillianRIC — Mon, 20 Jan 2025 04:31:43 GMT

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

Re: Display each name and latest status by time

Itay113 — Mon, 20 Jan 2025 11:11:58 GMT

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

Re: Display each name and latest status by time

JandreKillianRIC — Mon, 20 Jan 2025 11:13:27 GMT

Hi @Itay113

FirstSortedValue(

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

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

)

Re: Display each name and latest status by time

Itay113 — Mon, 20 Jan 2025 13:49:04 GMT

Works, thanks 😁