How to use Qlikview dashboard variables in SQL script used to load data

[#1]

Hello,

I have a variable vuserdate where user inputs date in the dashboard (eg: '28-mar-2016') and 4 other variables - prevday,7prevday,prevmonthday,prevyearday which are derived from vuserdate.

I want to refer these variables in SQL query in Script editor so that Qlikview only loads data for these 5 date variables instead of full year.

I tried following queries:

select a,b

from table 1

where date in ($(vuserdate), $(prevday),$(7prevday),$(prevmonthday),$(prevyearday))

and

select a,b

from table 1

where date in ('$(vuserdate)', '$(prevday)','$(7prevday)','$(prevmonthday)','$(prevyearday)')

[jonathandienst]

You need to ensure that the variables are formatted with the correct date format for your SQL server. Try the query with the literal values first:

select a,b

from [table 1]

where date in ('28-mar-2016', '27-mar-2016','21-mar-2016','28-feb-2016','28-mar-2016');

If that works, then you know that the date format is OK. Then ensure that the variables are being formatted in the way you tested. The second script with the quotes is correct, but I suspect your date format is incorrect for SQL DB server or your variables contain incorrect values. If you run in debug mode you can confirm that the variables are being set correctly and see the query actually passed to the SQL DB.

[#3]

Hi Jonthan,

Query works fine in following format

select a,b

from [table 1]

where date in ('28-mar-2016', '27-mar-2016','21-mar-2016','28-feb-2016','28-mar-2016');

But the problem is DB stores data in timestamp format, hence I need to do conversion when I am defining other 4 variables based on vuserdate.

I tried running SQL query in debug mode turns out Query is recognizing the vuserdate but does not do further derivation of the variables.

my formula for Previous date : =date((vEndDate)-if(date(vEndDate),'WWW')='Mon',3,1)

also tried : =date(date#(vEndDate,'DD-MMM-YYYY')- if(date(date#(vEndDate,'DD-MMM-YYYY'),'WWW')='Mon',3,1) ,'DD-MMM-YYYY')

the second format displays the date correctly in the Dashboard.