topic Monthtodate variable problem in QlikView

Monthtodate variable problem

Wed, 26 Aug 2009 15:26:30 GMT

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

Monthtodate variable problem

stephencredmond — Wed, 26 Aug 2009 16:12:40 GMT

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

Monthtodate variable problem

Wed, 26 Aug 2009 16:45:01 GMT

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.

Monthtodate variable problem

stephencredmond — Wed, 26 Aug 2009 17:10:07 GMT

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

Monthtodate variable problem

Wed, 26 Aug 2009 17:46:25 GMT

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?

Monthtodate variable problem

stephencredmond — Wed, 26 Aug 2009 21:21:46 GMT

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