topic Re: max value of max date in App Development

max value of max date

JMAROUF — Tue, 30 Nov 2021 18:17:33 GMT

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:

Re: max value of max date

MayilVahanan — Tue, 13 Jul 2021 05:18:29 GMT

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:

Re: max value of max date

tresB — Tue, 13 Jul 2021 05:24:01 GMT

You actually need a simpler expression for this: 😊

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

Re: max value of max date

JMAROUF — Tue, 13 Jul 2021 10:42:54 GMT

Hi @tresB ,

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.

Re: max value of max date

JMAROUF — Tue, 13 Jul 2021 10:53:24 GMT

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),

Re: max value of max date

MayilVahanan — Tue, 13 Jul 2021 11:09:02 GMT

Might be, try like below

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