Count status latest date

[MatsR]

Hi all, I'm new to Qlik sense and I want help creating a expression.

I need to count the number of status within an chosen intervall (for this example 2025-01-01 - 2025-01-20)

for that status with the latest date grouped by the key-column (the rows in the table below with bold text)

In this case I whould like the result to be: Status: Finish : 1, Status: InProg : 1

Key     Status      Date

AA       Start        2025-01-01

AA       InProg     2025-01-10

AA       Finish    2025-01-15

BB       Start        2025-01-10

BB       InProg    2025-01-12

[Clement15]

Hello,

You can use a code like this to have a new field that meets your need

TEST:
LOAD * INLINE [
Key, Status, Date
AA, Start, 2025-01-01
AA, InProg, 2025-01-10
AA, Finish, 2025-01-15
BB, Start, 2025-01-10
BB, InProg, 2025-01-12
];

left Join

LOAD
Key,
Max(Date) AS Date,
FirstSortedValue(Status, -Date) AS LastStatus
RESIDENT TEST
GROUP BY Key;

[MatsR]

Thanks for your reply, will this work if the user changes the intervall in the report via lets say a filter? 

[Clement15]

Hello,

To make it dynamic you can not do it in the script but use this measure in a table 

FirstSortedValue(Status, -Date)