topic Identify max date for every ID field. in App Development

Identify max date for every ID field.

Krish — Mon, 03 Jul 2023 10:50:25 GMT

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

Re: Identify max date for every ID field.

udit_k — Mon, 03 Jul 2023 11:24:12 GMT

use below expression:-

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

Re: Identify max date for every ID field.

Krish — Mon, 03 Jul 2023 11:32:09 GMT

@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.

Re: Identify max date for every ID field.

udit_k — Mon, 03 Jul 2023 11:45:21 GMT

use below expression and uncheck Include zero value in data handling

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

Re: Identify max date for every ID field.

BrunPierre — Mon, 03 Jul 2023 20:30:37 GMT

@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;

Re: Identify max date for every ID field.

Krish — Mon, 03 Jul 2023 12:51:29 GMT

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.

Re: Identify max date for every ID field.

Krish — Mon, 03 Jul 2023 12:52:23 GMT

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

Re: Identify max date for every ID field.

udit_k — Mon, 03 Jul 2023 14:11:40 GMT

use below exp in dimension :-

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

Re: Identify max date for every ID field.

BrunPierre — Mon, 03 Jul 2023 20:32:33 GMT

Format the date field properly,

Then try this;

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