topic Re: Selecting Specific Data Based on Date in QlikView

Selecting Specific Data Based on Date

Tue, 19 Jul 2016 13:21:56 GMT

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

Re: Selecting Specific Data Based on Date

Josh_Good — Tue, 19 Jul 2016 22:45:02 GMT

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

Re: Selecting Specific Data Based on Date

Wed, 20 Jul 2016 10:53:05 GMT

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

Re: Selecting Specific Data Based on Date

Josh_Good — Wed, 20 Jul 2016 15:53:26 GMT

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

Re: Selecting Specific Data Based on Date

Fri, 22 Jul 2016 07:36:24 GMT

yep that works, thank you a lot josh.

Re: Selecting Specific Data Based on Date

Fri, 22 Jul 2016 08:25:37 GMT

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

Re: Selecting Specific Data Based on Date

Mon, 03 Jul 2017 14:31:48 GMT

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

Re: Selecting Specific Data Based on Date

Josh_Good — Mon, 03 Jul 2017 16:27:50 GMT

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