4 Replies Latest reply: Jul 12, 2011 6:04 AM by Martin FAVIER RSS

    Calendar Script - Debug Help Needed

    stuart wannop

      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
          Martin FAVIER

          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
              Martin FAVIER

              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