Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JMAROUF
Creator II
Creator II

max value of max date

Hi everyone,

i want to get the max of the position of the max date value, i should use set analysis with firstsortedvalue, can any one help please? i have joined a capture as exemple of data, i want chose the max position of the max date of type 1:

this expression doesn't work:  max({<date={"$(=firstsortedvalue({<type={1}>} date,-date))"}>} position)

desired lignes are colored with yellow:

JMAROUF_0-1626113157809.png

 

Labels (1)
1 Solution

Accepted Solutions
MayilVahanan

Hi 
Try like below

fund:
LOAD * INLINE [
fund, date, position, type
F1, 01/01/2021, 10, 1
F1, 01/12/2021, 9, 1
F1, 01/12/2021, 5, 1
F2, 01/03/2020, 7, 1
F2, 01/03/2020, 18, 2
F2, 01/03/2020, 19, 1
];
Join
Load fund, max(date) as date, 1 as flag Resident fund where type = 1 Group by fund;

O/p:

MayilVahanan_0-1626153501900.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

5 Replies
MayilVahanan

Hi 
Try like below

fund:
LOAD * INLINE [
fund, date, position, type
F1, 01/01/2021, 10, 1
F1, 01/12/2021, 9, 1
F1, 01/12/2021, 5, 1
F2, 01/03/2020, 7, 1
F2, 01/03/2020, 18, 2
F2, 01/03/2020, 19, 1
];
Join
Load fund, max(date) as date, 1 as flag Resident fund where type = 1 Group by fund;

O/p:

MayilVahanan_0-1626153501900.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
tresesco
MVP
MVP

You actually need a simpler expression for this: 😊

firstsortedvalue({<type={1}>} position,-date)

JMAROUF
Creator II
Creator II
Author

Hi @tresesco ,

thank you for the answer, however the formula doesn't work, i have a complicated set analysis in a star schema, the picture above is just for explaning the need, also i need the max position of the max date.

JMAROUF
Creator II
Creator II
Author

Hello @MayilVahanan ,

is there any way to achieve this without touch to the dataset, we have some requirements so we should have simple selects in load script (avoiding joins), 

MayilVahanan

Might be, try like below

If(date = Max(Total<fund> date), max({<type={1}>}position))

MayilVahanan_0-1626174536238.png

 

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.