3 Replies Latest reply: Jan 31, 2018 8:16 AM by Ali Hijazi RSS

    Issue with mismatching date formats

    Alex Tomlins

      Qlik Sense Gurus Assemble!


      Hi guys,


      I am having trouble associating on a date field between my master calendar date field and my fact table.


      I have got a date time data type but is but the data is aggregated by hour so e.g '31/01/2018 01:00:00:00'


      I want to get my master calendar is currently coming out as  ''31/01/2018 01:00:00:11' and so there is no link.


      Has anyone got any advice for me pretty please?


      the master calendar script is as follows, the date hour is in bold - this is the one I am struggling to get to '31/01/2018 01:00:00:00' ;




      min(SubscribedDateTime) as minDate, 

      max(SubscribedDateTime) as maxDate 

      Resident Subscriptions; 


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

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

      LET vDays = varMaxDate - varMinDate + 2;



      Date(Floor(DateHour)) AS Date,


        Hour(DateHour) AS Hour,

        Week(DateHour) AS Week,

        Year(DateHour) As Year, 

        Month(DateHour) As Month,

        MonthName(DateHour) As MonthName,

        WeekYear(DateHour) AS [WeekYear],

        Month(DateHour) & '-' & Year(DateHour) as MonthYear, 

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

        WeekDay(DateHour) as WeekDay,

        Minute(DateHour) AS Minute;


      Timestamp(Timestamp($(varMinDate)) + ((RecNo() - 1)/24)) as DateHour


      AUTOGENERATE 24 * $(vDays);

      DROP Table Temp;