Selecting Specific Data Based on Date

[#1]

Thanks Josh. Great video.

I am new in qlik and the qlik community really helps me a lot.

I still have a problem you may help me with.

I want to get the grade for my date just below the max date for an id ,in the exemple

exemple:

id:11 ; date : 01/10/2016  ; grade: A

id: 11 ; date : 13/12/2016 ; grade : B

id:13 ;  date : 07/04/2016  ; grade : C .....

how to select the date just below the max date for my id 11 ?

thank you in advance

[Josh_Good]

Hi Yassine,

I branched this to a new thread because it is a new question.  This way other will be able to find it easier.

I'm not clear on what you are trying to do.  You can simply select the date you are interested in and the associated data will be displayed.  However, I'm thinking you want to do something more complex, such as select the second highest date.  Can you post a more detailed example?

-Josh

Qlik

[#3]

Hi Josh,

First thank you for the replay ,

Basically I need to compare the evolution of the grade by date for a single person(id). So the idea was to by selecting one id  I can get the grade for the last date and the grade for the date juste below ;

in my exemple :

id:11 ; date : 01/10/2016  ; grade: A

id: 11 ; date : 13/12/2016 ; grade : B

id:13 ;  date : 07/04/2016  ; grade : C .....

by selecting id 11 ,the output will be :

last date: 13/12/2016 ; grade:B

prevDate: 01/10/2016 ; grade: A

so i can compare the value and see that the grade has decreased from A to B now.

In a basic programming logic I'll have done this :

maxDate = max(date);

dateBellowMaxDate =  (maxDate ) -1

if( id.maxDate.grade > id.dateBellowMaxDate.grade)

{ grade increased }

else

{ grade decreased}

I hope you understanded me and maybe can help me

Thank you again

[Josh_Good · Accepted solution]

Hi Yassine,

For the most recent grade this expression will work:

Concat({$<Date = {$(=chr(39) & Date(Max(Date)) & chr(39))}>} Grade)

For the previous grade this expression will work:

Concat({$<Date = {$(=chr(39) & Date(Max(Date,2)) & chr(39))}>} Grade)

Note I have made the assumption that you will have selected on a single id.

I hope this helps.

-Josh

Qlik

[#5]

yep that works, thank you a lot josh.

[#6]

but in qlik sense no need of chr() fonction. I did it like this

if(Concat({$<date = {$(=Max(date,2))}>}grade) > Concat({$<date = {$(=Max(date,1))}>} grade), 'increased','decreased')

[#7]

Hi ,

jgd‌

I was able to apply arbritary date range s explained by you.But i am not able to implement different selection across different sheets.I tried using alternate states and set analysis but its not workign across different sheets as different selections.

Please help me resolve this issue.

thanks

Amansh

[Josh_Good]

Amansh,

Please create a new posting detailing that you have done and what is not working as you intended.  This will enable the entire community (including me) to help you.

-Josh

Qlik