New to Qlik Sense

[danielrogra]

Hi all

I'm facing an issue I can't solve although there is some information on internet....

I want to create a variable with contains just a date like this:

LET vMaxDate = peek('MaxDate');

Then, I want to use it in a SQL where clause, like this:

and datediff(mm,start_date,$(vMaxDate)) between 0 and 11

But it's not working...I've also tried doing the following:

and datediff(mm,start_date,'$(vMaxDate)') between 0 and 11

But still not working....I'm sure there is something I'm missing but I am not able to see it.

Does anyone know how to fix it? Thanks in advance

Daniel

[sunny_talwar]

What is the format of vMaxDate... may be you need TO_CHAR() or TO_DATE() to help with the interpretation within the SQL statement

[danielrogra]

Thanks for your quick reply

I've tried using to_date like follows: datediff(mm,start_date,To_date('$(vMaxDate)')) between 0 and 11

but still not working.....The variable is like this:

[sunny_talwar]

How about this

DATEDIFF(mm,start_date, TO_DATE('$(vMaxDate)', 'DD/MM/YYYY H:MI:SS')) between 0 and 11

[danielrogra]

Thank you very much.

Unfortunately it's still not working...Maybe I'm doing something wrong in the peek function?

Regards

[danielrogra]

Any other idea? Thanks in advance

[sunny_talwar]

Can you share the script where you create the field MaxDate to use within Peek() function?

LET vMaxDate = peek('MaxDate');

[danielrogra]

Sure Sunny.

Temp:

Load date as MaxDate;

select distinct date from tableA a inner join TableB b on ....; (This load just one date)

[sunny_talwar]

Try this

Temp:

Load Date(date, 'DDMMMYYYY') as MaxDate;

select distinct date from tableA a inner join TableB b on ....;

LET vMaxDate = peek('MaxDate');

and then use this

and datediff(mm,start_date,'$(vMaxDate)') between 0 and 11

DDMMMYYYY is my guess format, are you using ORACLE database?

[danielrogra]

Not working....I've used this format: 'DD/MM/YYYY H:MI:SS', the same that in the database...