Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Monthtodate variable problem

Hi *,

I read the blog entry from Stephen Redmond about Yeartodate.

I'm setting the today date in a variable v_today.

Yeartodate and last yeartodate are working fine:

YearToDate(Date , 0, 1, $(v_today))*-1 AS Cal_YTD_Current
YearToDate(Date , -1, 1, $(v_today))*-1 AS Cal_YTD_Previous

AS soon as I'm working with Monthtodate and a variable instead of today() I stumble:

YearToDate(Date , 0, Month(today()))*-1 AS Cal_MTD_Current,
YearToDate(Date , -1, Month(today()))*-1 AS Cal_MTD_Previous1,
YearToDate(Date , -2, Month(today()))*-1 AS Cal_MTD_Previous2

-> works fone

Now I tried to use a variable instead of today e.g. v_date = '01.02.2008'

YearToDate(Date , 0, Month($(v_date)))*-1 AS Cal_MTD_Current,
YearToDate(Date , -1, Month($(v_date)))*-1 AS Cal_MTD_Previous1,
YearToDate(Date , -2, Month($(v_date)))*-1 AS Cal_MTD_Previous2

I'm getting this:

Cal_MTD_Current: only 0
Cal_MTD_Previous1: Feb, Mar 08
Cal_MTD_Previous2: Feb - Aug 07

Can someone tell me what's wrong with this. Thank you.

Regards

5 Replies
stephencredmond
Luminary Alumni
Luminary Alumni

Hi,

This appears to be related to your other post.

Try:


YearToDate(Today() , 0, Month('$(v_date)'))*-1;


Dates as variables in script can be tricky. I prefer to cast them as Numbers because then I don't have to mess around with string quotes like this:


LET v_date = num(date#('08/14/2009','MM/DD/YYYY'));


Stephen

Not applicable
Author

Hi Stephen,

thanks for your help. It works perfect as long as my today variable is in current month (Aug).
As soon as I set my v_date to e.g. 01.02.2009 I get all months to current. Did I misunderstand something?
Thanks.

stephencredmond
Luminary Alumni
Luminary Alumni

Hi,

When you specify a start month in the YearToDate function (the default is, obviously, 1), the function will only return true of the date passed is in that month or subsequent months. Basically what it is doing is shifting the beginning of the year from 1 = January to another month.

Does that help?

Stephen

Not applicable
Author

Hi Stephen,

yes and no 😉

I'm trying to set the enddate manually. Instead of today() I'm trying to use the value of v_date.

e.g. if v_date = '15.07.2009' the sales values from 01.07.2009 to 15.07.2009 should be used.
Did I get something wrong concerning the use of monthtodate?

stephencredmond
Luminary Alumni
Luminary Alumni

Hi,

YearToDate only gives you the option to specify a month number - not a particular date in that month.

For that level of fine control, you need to use InMonthToDate:

InMonthToDate ( date, basedate , shift )

Stephen