Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
stuwannop
Contributor II

Calendar Script - Debug Help Needed

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;

1 Solution

Accepted Solutions
martin59
Valued Contributor II

Re: Calendar Script - Debug Help Needed

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

4 Replies
martin59
Valued Contributor II

Re: Calendar Script - Debug Help Needed

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

martin59
Valued Contributor II

Re: Calendar Script - Debug Help Needed

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

stuwannop
Contributor II

Re: Calendar Script - Debug Help Needed

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

martin59
Valued Contributor II

Re: Calendar Script - Debug Help Needed

Yes, you can use a variable like this :

LET vToday = Today();

Martin

Community Browser