Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
use below expression:-
Date(sum(total<ID>aggr(date(max([Start Date])),ID)))
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.
use below expression and uncheck Include zero value in data handling
aggr(date(max([Start Date])),ID)
@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;
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.
I need both rows for other calculations. I cannot make the changes in script level.
use below exp in dimension :-
if(Date((aggr(date(max([Start Date])),[ID])))>0,'Y')
Format the date field properly,
Then try this;
=Only(Aggr(If([Start Date] = Max(TOTAL <ID> [Start Date]),[Start Date]), ID, [Start Date]))