Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
JMAROUF
Creator
Creator

Max value of the max previous date

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:

JMAROUF_1-1632837054094.jpeg

 

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?

Labels (1)
  • Other

1 Solution

Accepted Solutions
rubenmarin

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

View solution in original post

2 Replies
rubenmarin

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

View solution in original post

JMAROUF
Creator
Creator
Author

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