Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Itay113
Contributor II
Contributor II

Display each name and latest status by time

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

Labels (3)
1 Solution

Accepted Solutions
JandreKillianRIC
Partner Ambassador
Partner Ambassador

Hi @Itay113 

 

FirstSortedValue(

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

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

)

Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel | Follow me on LinkedIn

View solution in original post

4 Replies
JandreKillianRIC
Partner Ambassador
Partner Ambassador

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

Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel | Follow me on LinkedIn

Itay113
Contributor II
Contributor II
Author

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

JandreKillianRIC
Partner Ambassador
Partner Ambassador

Hi @Itay113 

 

FirstSortedValue(

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

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

)

Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel | Follow me on LinkedIn

Itay113
Contributor II
Contributor II
Author

Works, thanks 😁