Identify max date for every ID field.

[Krish]

Hi,

 I have a chart where I need to identify the row which contains the max date. 

Expression used:

if(Aggr(
Count({<[EVENT]={'AIR'},[Month Num]={'<=$(#vSelectedMonthNumber)'},[Start Date]= {"$(=Max([Start Date]))"},[Event Month]=>} DISTINCT [ID])
,[ID],[Start Date])>0,'Y')

Input:

ID	Start Date
1	01/01/2023
1	10/01/2023
2	22/01/2023
3	15/12/2023
3	03/04/2023
4	10/19/2023

Output:

ID	Start Date	Expr
1	10/01/2023	Y
2	22/01/2023	Y
3	15/12/2023	Y
4	10/19/2023	Y

[udit_k]

use below expression:-

Date(sum(total<ID>aggr(date(max([Start Date])),ID)))

[Krish]

@udit_k 

  Thanks for your response. The final output should display only the row which has max date. In above example, for ID 1, it should show 10/01/2023 which is the max date ( 1 row)  and for ID 2 10/01/2023 etc. I do not want to show 01/01/2023 row item.

[udit_k]

use below expression and uncheck Include zero value in data handling

aggr(date(max([Start Date])),ID)

[BrunPierre]

@Krish I would do something like that in a script.

Data:
Load * Inline [
ID,Start Date
1, 01/01/2023
1, 10/01/2023
2, 22/01/2023
3, 15/12/2023
3, 03/04/2023
4, 10/19/2023];

Inner Join
LOAD ID,
Max("Start Date") as "Start Date",
'Y' as Expr

Resident Data
Group By ID;

Exit Script;

[Krish]

I am using QlikView.  I tried this in the chart but it doesn't give the intended results.

if(date(Count(TOTAL<[ID]>aggr(date(max([Start Date])),[ID])))>0,'Y')

It still gives me 2 rows. 

[Krish]

I need both rows for other calculations. I cannot make the changes in script level.

[udit_k]

use below exp in dimension :-

if(Date((aggr(date(max([Start Date])),[ID])))>0,'Y')

[BrunPierre]

Format the date field properly,

Then try this;

=Only(Aggr(If([Start Date] = Max(TOTAL <ID> [Start Date]),[Start Date]), ID, [Start Date]))