Assign variable with select statement

[#1]

Im having problems with this.

I have this qlikview script which is run on the 15th of every month. This script intends to automatically take the last date of the previous month in the format of 'YYYY-MM-DD'. For example today is 15th January 2017. I will run my script and it will give me a date of 31th December 2016 in the format 2016-12-31. If it is 15th December 2016, I will get 30th November 2016 in the format 2016-11-30.

I want to set the date retrieved as a variable in the format yyyy-mm-dd to be used in another query.

Basically I tried

SET @vLastDate = DATE_FORMAT(select last_day(curdate() - INTERVAL 1 MONTH), '%Y-%m-%d');

When I checked the variable values in settings, I get

value is DATE_FORMAT(select last_day(curdate() - INTERVAL 1 MONTH), '%Y-%m-%d')

but what I want is the last date value itself.

I am doing this in qlikview. Please help me identify my problem thank you.

[johnw]

If you want the date value itself in a variable in QlikView, you would need to use QlikView functions, not whatever you would use in the select statement if you were coding it there instead. Also a LET instead of SET, because LET evaluates the expression, while SET just takes it literally. Maybe this.

LET @vLastDate = date(monthstart(today())-1,'YYYY-MM-DD');

[rahulpawarb]

I am 100% agree with John. if the variable needs to calculate values based on some expression then use LET or else you can use SET assign hard code value to it. Also, refer the QlikView function(s) at variable declaration/assignment. If you still want to go ahead with SQL compliant functions then load the SQL expression into a table and assign it to a variable using PEEK function.

Hope this makes sense.

Regards!

Rahul