Bizarre calendar behaviour

[#1]

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

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');

[#2]

Thanks Jonathan. You're a legend! A couple of questions

1) How did you spot the time stamp of 23:00:00?

2) I have had this integration with Qlikview and Salesforce for a couple of years and this has never been an issue. Do you think it could be the new Salesforce connector?

[qlikmsg4u]

Convert activity date to Timestamp you will find the time values

[jonathandienst]

Alex Hamilton McLeod wrote:

1) How did you spot the time stamp of 23:00:00?

2) I have had this integration with Qlikview and Salesforce for a couple of years and this has never been an issue. Do you think it could be the new Salesforce connector?

1) Following a hunch, I displayed the dates in timestamp format

2) I suspect that the business process has changed and taht is causing Salesforce to add a time value - but that is just one possible cause...

[MarcoWedel]

Hi,

maybe it's some daylight saving time conversion issue:

hope this helps

regards

Marco