Max value of the max previous date

[JMAROUF]

Hi everyone,

i have a star schema with a multikpi visualisation and i want to get the max position (rank_invest)  of the max previous date (rank_invest)  of the category 1 (investor) for each invested (dimension) , here is an example of the model:

the output desired:  if the user selects 26/09/2021 then:

- invested_10 : 1,3  (max last date 25, max position 1,3 , cat_1)

- invested_20 : 1,8 (max last date 25, max position 1,8 , cat_1)

.... 

 i have created the  field autonumber(date) as IdDate and  tried this: 

max({<category={'cat_1'},IdDate ={"$(=max(IdDate ))-1"}>} position) but it doesn't work, i realised that just rows with the max value of IdDate  are concerned, others didn't have any value, i couldn't choose the max value/invested.

Can anyone help please?

[rubenmarin · Accepted solution]

Hi, you can try with a table with:

Dimension= id_invested

Expression= Max(Aggr(If(date=Max(TOTAL <id_invested> date,2), Max(position)),id_invested,date))

Note that I avoided set analysis because each id_invested could have a different 2nd max date.

If the date should be the same for all id_invested you can add it in set analysis, but it will be better to use Max(dateField,2) to return the 2nd max value, substracting always 1 day can return a date without values

[JMAROUF]

Thank you @rubenmarin,  i will try as soon as possible and accept it as solution if it works.