9 Replies Latest reply: Jul 31, 2017 9:44 AM by Matteo Pietrucci RSS

    Issues With Master Calendar

    Alison Cooke

      Hello All,

       

      I am hoping you can help me with the present issue.

       

      I have this script:

       

      Screenshot (56).png

      And this is the script I am using for the master calendar:

      Screenshot (57).png

      I get this error:

       

      Invalid Autogenerate count: -1

      The error occurred here:

      MasterCalendar: LOAD Distinct text(num(MonthStart(Date( + RecNo() - 1)))) AS [Date], Year(Date( + RecNo() - 1)) as [Year], Month(Date( + RecNo() - 1)) as [Month], ceil(month(Date( + RecNo() - 1))/3) as Quarter, Date(monthStart(Date( + RecNo() - 1)), 'MM-YYYY') AS [MonthYear] AUTOGENERATE ( - +1)

       

      And this is the result I get in the UI:

      Screenshot (58).png

      Why is this? And how do I resolve it?

       

      Thank you in advance,
      Alison

        • Re: Issues With Master Calendar
          Arvind Patil

          HI Alison,

           

          Please refer below master Calender:

           

          QuartersMap: 

          MAPPING LOAD  

          rowno() as Month, 

          'Q' & Ceil (rowno()/3) as Quarter 

          AUTOGENERATE (12); 

           

          Temp: 

          Load 

                         min(VALIDDATE) as minDate,  //Your Date Field

                         max(VALIDDATE) as maxDate  //Your Date Field

          Resident Facts; 

           

          Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

          Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

          DROP Table Temp; 

           

          TempCalendar: 

          LOAD 

                         $(varMinDate) + Iterno()-1 As Num, 

                         Date($(varMinDate) + IterNo() - 1) as TempDate 

                         AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

           

          MasterCalendar: 

          Load 

                         TempDate AS VALIDDATE, 

                         week(TempDate) As Week, 

                         Year(TempDate) As Year, 

                         Month(TempDate) As Month, 

                         Day(TempDate) As Day, 

                         YeartoDate(TempDate)*-1 as CurYTDFlag, 

                         YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                         inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                         date(monthstart(TempDate), 'MMM-YYYY') as MonthYear, 

                         ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

                         Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

                         WeekDay(TempDate) as WeekDay 

          Resident TempCalendar 

          Order By TempDate ASC; 

          Drop Table TempCalendar; 

           

          Thanks,

          Arvind Patil

            • Re: Issues With Master Calendar
              Alison Cooke

              Hello Arvind,

               

              Thank you for your response. But, I am getting this error:

              The following error occurred:

              Table 'Facts' not found

              The error occurred here:

              Temp: Load min(Date) as minDate, max(Date) as maxDate Resident Facts

                • Re: Issues With Master Calendar
                  Matteo Pietrucci

                  In Temp table you must calculate min & max of datefield of your fact table (resident it)

                   

                  In your case..
                  Temp:

                  Load

                                 min([Merchandise Date])  as minDate,  //min date of Merch.

                                 max([Merchandise Date]) as maxDate  //max date of Merch.

                  Resident [Merchanise Income];

                   

                  //Save min/max in variables from Temp Table and drop it

                  Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

                  Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

                  DROP Table Temp;

                   

                  //Generate Calendar from minDate to MaxDate

                  TempCalendar:

                  LOAD

                                 $(varMinDate) + Iterno()-1 As Num,

                                 Date($(varMinDate) + IterNo() - 1) as TempDate

                                 AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

                   

                  //Create Full Calendar field and drop sourcetable TempCalendar

                  MasterCalendar:

                  Load

                                 TempDate AS VALIDDATE,

                                 week(TempDate) As Week,

                                 Year(TempDate) As Year,

                                 Month(TempDate) As Month,

                                 Day(TempDate) As Day,

                                 YeartoDate(TempDate)*-1 as CurYTDFlag,

                                 YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

                                 inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

                                 date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

                                 ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

                                 Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

                                 WeekDay(TempDate) as WeekDay

                  Resident TempCalendar

                  Order By TempDate ASC;

                  Drop Table TempCalendar;

                    • Re: Issues With Master Calendar
                      Alison Cooke

                      Hi Matteo,

                       

                      Thank you, I am getting the following error however:

                       

                      The following error occurred:

                      Field '<=' not found

                      The error occurred here:

                      ?

                        • Re: Issues With Master Calendar
                          Matteo Pietrucci

                          1) Which format has your Date field from Merchandise Sheet (source file)?
                          2) Can you debug (or exit) before create TempCalendar in the script?

                           

                           

                          Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

                          Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

                          DROP Table Temp;


                          ADD THIS

                          EXIT SCRIPT;

                          and check values of variables: varMinDate varMaxDate


                          I think that  these are not create correctly in the script.


                          PS: U can test it after reload -> Variable Section (in presentation, botton left).

                  • Re: Issues With Master Calendar
                    Alison Cooke

                    I seem to have been able to get rid of the autogenerate error. Now my problem is different.

                     

                    Screenshot (59).png

                     

                    As you can see, three dates are being generated: 42917, 42934 and 42935. This is strange as in the data source, there are only two dates; 42935 and 42934. Where is 42917 coming from?

                    • Re: Issues With Master Calendar
                      Matteo Pietrucci

                      Can u attach .qvf of your app?