13 Replies Latest reply: Sep 10, 2015 6:45 AM by Marco Wedel RSS

    Bizarre calendar behaviour

      I have the following chart below, which is behaving oddly since i upgraded the Salesforce connector and made some minor changes.

       

      EVERY record has a date associated with it, yet there is NULL column appearing at the end. What's even more strange is that only the months Nov through to Feb are missing!

      interactions.jpg

      My calendar script is as follows

       

       

      ActivityCalendar:

      LOAD

            ActivityDate,

             year(ActivityDate) as [Activity Year],

             month(ActivityDate) as [Activity Month],

             monthname(ActivityDate) as [Activity MonthYear],

             if(len(Week(ActivityDate))=2,Year(ActivityDate) & Week(ActivityDate),

             Year(ActivityDate) & '0' & Week(ActivityDate)) as [Activity YEARWEEK],

             weekstart(ActivityDate) as [Activity Week],

             week(ActivityDate) as [ActivityWeekNumber],

             day(ActivityDate) as [Activity Day],

           

             if(if(len(Week(Today(1)))=2,Year(Today(1)) & Week(Today(1)),Year(Today(1)) & '0' & Week(Today(1)))-1=if(len(Week(ActivityDate))=2,Year(ActivityDate) & Week(ActivityDate),

             Year(ActivityDate) & '0' & Week(ActivityDate)),1,0) as ActivityIsCurrentWeek,

           

             if(if(len(Week(Today(1)))=2,Year(Today(1)) & Week(Today(1)),Year(Today(1)) & '0' & Week(Today(1)))-2=if(len(Week(ActivityDate))=2,Year(ActivityDate) & Week(ActivityDate),

             Year(ActivityDate) & '0' & Week(ActivityDate)),1,0) as ActivityIsPriorWeek

      ;

      // Generate range of dates between min and max.

      LOAD

             date(DateMin + IterNo()) as ActivityDate            // Link Field

      WHILE DateMin + IterNo() <= DateMax    

      ;

      // Find min and max of date field values.

      LOAD

             min(datefield)-1 as DateMin

             ,max(datefield) as DateMax

      ;

      // Load date field values.

      LOAD

             FieldValue('ActivityDate', RecNo()) as datefield

      AutoGenerate FieldValueCount('ActivityDate');