topic Re: Calendar Script - Debug Help Needed in QlikView

Calendar Script - Debug Help Needed

stuwannop — Tue, 12 Jul 2011 09:19:08 GMT

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;

Re: Calendar Script - Debug Help Needed

martin59 — Tue, 12 Jul 2011 09:29:33 GMT

Hi,

I'm not sure but it could be this :

Range:
LOAD
 min(Date) as startdate,
 max(Date) as enddate
resident Invoice;
//Peek out the values for later use
let vStart = num(peek('startdate',-1,'Range')-1);
let vEnd = num(peek('enddate',-1,'Range'));
let vRange = $(vEnd) - $(vStart);

Hope that helps you

Re: Calendar Script - Debug Help Needed

martin59 — Tue, 12 Jul 2011 09:35:19 GMT

Excuse me, I've found your error.

Where do you declare you variable vToday ? This variable hasn't value.

However, you can use the today() function like this

//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, today(), -1),1) as Cal_FULL_LY, // All Dates Last Year
// if(InYear (Date, today(), 0),1) as Cal_FULL_TY, // All Dates This Year
    if(InYearToDate (Date, today(), 0),1,0) as Cal_YTD_TY,  // All Dates to Date this Year
    if(InYearToDate (Date, today(), -1),1,0) as Cal_YTD_LY,  // All Dates to Date Last Year
    
    if(InQuarterToDate (Date, today(), 0),1,0) as Cal_QTR_TQ,
    if(InQuarterToDate (Date, today(), -1),1,0) as Cal_QTR_LQ,  
    
    if(InMonthToDate(Date, today(), 0),1,0) as Cal_MNTH_TM,
    if(InMonthToDate(Date, today(), -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;

Hope that helps you

Re: Calendar Script - Debug Help Needed

stuwannop — Tue, 12 Jul 2011 09:51:39 GMT

Thanks a lot Martin that's worked - really appreciate your help and such a speedy response.

Would I be right in thinking if I wanted to use it as a variable I would just declare:

Let vtoday = today()

Not sure if there would be any point though - just curious

Stu

Re: Calendar Script - Debug Help Needed

martin59 — Tue, 12 Jul 2011 10:04:56 GMT

Yes, you can use a variable like this :

LET vToday = Today();

Martin