Solved: Re: Problem with Calendar Script - Qlik Community

zagzebski · ‎2014-01-03

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,

Anonymous · ‎2014-01-03

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.

View solution in original post

Nicole-Smith · ‎2014-01-03

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

rustyfishbones · ‎2014-01-03

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

rustyfishbones · ‎2014-01-03

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

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

zagzebski · ‎2014-01-03

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.

Nicole-Smith · ‎2014-01-03

What date is it supposed to be returning?

rustyfishbones · ‎2014-01-03

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

zagzebski · ‎2014-01-03

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!

rustyfishbones · ‎2014-01-03

Just use

LET vYTD = MAX(Year);

zagzebski · ‎2014-01-03

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.