Hi All
I'm quite new to Qlikview (especially scripting) and I have a calendar script below (it's not mine) which should take the Max and Min dates and populate them in a calendar table. My table is called Invoicing, using the date field. When I run it, I am getting the error in green below. It looks like I'm missing a bracket somewhere - I've narrowed it down to the IF statements using the debugger - can anybody spot where I am going wrong?
Thanks for any help, the script is at the bottom of the post.
Stu
Error in expression:
')' expected
Calendar:
load
Date as Date,
Year(Date) as CalendarYear,
if(InYearToDate (Date, , 0),1,0) as Cal_YTD_TY,
if(InYearToDate (Date, , -1),1,0) as Cal_YTD_LY,
quartername(Date) as Cal_CalendarQuarter,
Month(Date)&'-'&right(year(Date),4) as Cal_MonthYear,
Month(Date) as Cal_Month,
Day(Date) as Cal_Day,
Week(Date) as Cal_Week,
Weekday(Date) as Cal_WeekDay
resident Date
SCRIPT:
// Dynamic Date range is built from data field - Date registered - change as required
// Replace all instances of MyDate with the key date field
// Replace instance of MyTable with the key date field resident table name
// All generated date fields will be prefaced with Cal_
Range:
LOAD
min(Date) as startdate,
max(Date) as enddate
resident Invoice;
//Peek out the values for later use
let vStart = peek('startdate',-1,'Range')-1;
let vEnd = peek('enddate',-1,'Range');
let vRange = $(vEnd) - $(vStart);
//Remove Range table as no longer needed
Drop table Range;
//Generate a table with a row per date between the range above
Date:
Load
$(vStart)+recno() as Date
autogenerate $(vRange);
//Calculate the Parts you need to examine
Calendar:
load
Date as Date,
// date(Date,'dd/mm/yyyy') as Cal_FullDate,
Year(Date) as CalendarYear,
'Q'&ceil(Month(Date)/3) AS Cal_Quarter,
// right(yearname(Date,0,$(vFiscalMonthStart)),4) as Cal_FiscalYear,
// if(InYear (Date, $(vToday), -1),1) as Cal_FULL_LY, // All Dates Last Year
// if(InYear (Date, $(vToday), 0),1) as Cal_FULL_TY, // All Dates This Year
if(InYearToDate (Date, $(vToday), 0),1,0) as Cal_YTD_TY, // All Dates to Date this Year
if(InYearToDate (Date, $(vToday), -1),1,0) as Cal_YTD_LY, // All Dates to Date Last Year
if(InQuarterToDate (Date, $(vToday), 0),1,0) as Cal_QTR_TQ,
if(InQuarterToDate (Date, $(vToday), -1),1,0) as Cal_QTR_LQ,
if(InMonthToDate(Date, $(vToday), 0),1,0) as Cal_MNTH_TM,
if(InMonthToDate(Date, $(vToday), -1),1,0) as Cal_MNTH_LM,
// YTD_LY, used in Expressions Ex. Sum(Sales*YTD_LY)
quartername(Date) as Cal_CalendarQuarter,
// quartername(Date,0,$(vFiscalMonthStart)) as Cal_FiscalQuarter,
// Month(Date)&'-'&right(yearname(Date,0,11),4) as Cal_FiscalMonthYear, //Fiscal!
Month(Date)&'-'&right(year(Date),4) as Cal_MonthYear,
Month(Date) as Cal_Month,
Day(Date) as Cal_Day,
Week(Date) as Cal_Week,
Weekday(Date) as Cal_WeekDay
resident Date;
//Tidy up
Drop table Date;
