Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
Thank you @rubenmarin, i will try as soon as possible and accept it as solution if it works.