topic Re: Problem with Calendar Script in QlikView

Problem with Calendar Script

zagzebski — Fri, 03 Jan 2014 14:03:22 GMT

I keep getting the following error message in my calendar script:don't see where there is a problem:

Error in expression: ')' expected

I have narrowed it down to this part of the script that is causing the problem:

LET vYTD= Floor(MakeDate(Year(Today()), (Month(Today())-1), Day(MonthEnd(Today()))-1));

If(InYearToDate(Date, $(vYTD), 0)
or InYearToDate(Date, '12/31/2013', -1)
or InYearToDate(Date, '12/31/2013', -2)
or InYearToDate(Date, '12/31/2013', -3)
or InYearToDate(Date, '12/31/2013', -4)
, 1, 0) as _YTD,

If(InYearToDate(Date, $(vYTD), 0)
or InYearToDate(Date, $(vYTD), -1)
or InYearToDate(Date, $(vYTD), -2)
or InYearToDate(Date, $(vYTD), -3)
or InYearToDate(Date, $(vYTD), -4)
, 'YTD') as YTD,

Re: Problem with Calendar Script

Nicole-Smith — Fri, 03 Jan 2014 14:36:27 GMT

The problem is with the let statement. What date are you trying to set it to?

Re: Problem with Calendar Script

rustyfishbones — Fri, 03 Jan 2014 14:39:42 GMT

Try changing the LET

LET vYTD = FLOOR(MAKEDATE(2014))

The MakeDate function will always default to the start of the year, so when you say 2014 it will give you back 1/1/2014 regardless

Re: Problem with Calendar Script

rustyfishbones — Fri, 03 Jan 2014 14:41:51 GMT

you could also do what you were doing above but just change it to

LET vYTD = Floor(MakeDate(Year(Today())));

Re: Problem with Calendar Script

zagzebski — Fri, 03 Jan 2014 14:55:33 GMT

Thanks for the response...

I am picking up someone else's code so just wondering - wouldn't changing this current variable

LET vYTD= Floor(MakeDate(Year(Today()), (Month(Today())-1), Day(MonthEnd(Today()))-1));

to your suggestion:

LET vYTD = Floor(MakeDate(Year(Today())));

ultimately change what the variable is returning (a different result). Sorry kind of new to the calendar.

Re: Problem with Calendar Script

Nicole-Smith — Fri, 03 Jan 2014 14:57:20 GMT

What date is it supposed to be returning?

Re: Problem with Calendar Script

rustyfishbones — Fri, 03 Jan 2014 15:03:32 GMT

to check if the variable works when you add it to a Text Object in the front end

Re: Problem with Calendar Script

zagzebski — Fri, 03 Jan 2014 15:04:02 GMT

Sorry Nicole - kind of still figuring that out. I am picking up where someone left off, At least I know it is the LET statement causing the problem. Now I just have to see what was intended with it!

Re: Problem with Calendar Script

rustyfishbones — Fri, 03 Jan 2014 15:09:30 GMT

Just use

LET vYTD = MAX(Year);

Re: Problem with Calendar Script

zagzebski — Fri, 03 Jan 2014 15:29:53 GMT

Wouldnt that give you 2014? January 2014 data isn't loaded as of today so Dec 2013 is still the latest data (YTD) data. Somehow I need to account for that.

Re: Problem with Calendar Script

Nicole-Smith — Fri, 03 Jan 2014 15:31:26 GMT

The following will give you the end date of last month:

LET vYTD = Monthend(Addmonths(today(),-1));

Re: Problem with Calendar Script

Anonymous — Fri, 03 Jan 2014 15:45:37 GMT

The makedate() in this expression is:

makedate(2014,0,30) - that is month is 0 (January minus 1), hence the error.

If you want it to be the end of the previous month, use

=monthend(today(),-1)

Re: Problem with Calendar Script

Fri, 03 Jan 2014 15:52:40 GMT

Hi,

You add expression in text box and check it out..

Re: Problem with Calendar Script

zagzebski — Fri, 03 Jan 2014 16:06:06 GMT

My YTD field doesn't populate with this variable:

LET vYTD =monthend(today(),-1)

If(InYearToDate(Date, $(vYTD), 0)
or InYearToDate(Date, $(vYTD), -1)
or InYearToDate(Date, $(vYTD), -2)
or InYearToDate(Date, $(vYTD), -3)
or InYearToDate(Date, $(vYTD), -4)
, 'YTD') as YTD

Re: Problem with Calendar Script

Anonymous — Fri, 03 Jan 2014 16:17:13 GMT

Change it a little, add floor():

LET vYTD =floor(monthend(today(),-1))

Edit: Some explanation.
Without floor, the variable returns date in format 12/31/2014. When you use it this way - $(vYTD), it is calculated as 12 divide by 31 divide by 2014. Not what you want. So, you either use floor to keep it numeric, as it was before (the value of this date is 41639), or change all your $(vYTD) in expression to simple vYTD, so it doesn't calculate. I think it is easier to change in one place.