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!


      My calendar script is as follows






             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.


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

      WHILE DateMin + IterNo() <= DateMax    


      // Find min and max of date field values.


             min(datefield)-1 as DateMin

             ,max(datefield) as DateMax


      // Load date field values.


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

      AutoGenerate FieldValueCount('ActivityDate');