Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JMAROUF
Creator
Creator

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

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

View solution in original post

tresesco
MVP
MVP

You actually need a simpler expression for this: 😊

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

JMAROUF
Creator
Creator
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
Creator
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