2 Replies Latest reply: Oct 9, 2017 11:20 AM by Justin Dallas RSS

    Master Calendar not matching actual data

    Mohammed Al Radi

      Dear Qlik Community,

       

      I hope this finds you well. I am currently working on an app that consists of 1 table with a date field in the MM/DD/YYYY format (I made sure to change the date format in the 'main' section of the data load editor). When I create a master calendar the dates can no longer be compared against the figures in either a table or bar/line graph.

       

      However, when I drag each date filter into a sheet  individually, everything looks right. I have used this master calender script for many apps with no problem, the only difference I can think of here is the MM/DD/YYYY format as opposed to the usual DD/MM/YYYY or numeric date formats.

      Please find enclosed for your review the script, can you think of any reason why they are not linking? Thank you for your support!

       

      [Dates]:

      LOAD

      [F2] as [Income Category],

      [F5] as [Session Name],

      [F7] as [Session Product],

      [F8] as [Session Quantity],

      [F9] as [Session Total Hours],

      Date#([F10]) as DayMonthYear,

      [F11] as [Session Expiry Date],

      [F12] as [Session Website],

      [F15] as [Session Status],

      [F16] as [Session Price],

      [F17] as [Session Transaction Fee],

      [F18] as [Session Total Revenue],

      [F19] as [Session Profit],

      [F20] as [Session Country],

      [F21] as [Session ISCOCountryCode],

      FROM [lib://Google Sheets]

      (html, codepage is 1252, embedded labels, table is @2);

       

      Temp:

      Load Date#([DayMonthYear]) as NumericDate

      RESIDENT [Dates];

      MinMax:

      LOAD

      Min(NumericDate) AS MinDate,

      Max(NumericDate) AS MaxDate

      RESIDENT Temp;

       

       

      LET vMinDate = Num(Peek('MinDate',0,'MinMax'));

      LET vMaxDate = Num(Peek('MaxDate',0,'MinMax'));

       

       

      MasterCalendar:

      LOAD

      Date#($(vMinDate) + RecNo() - 1) AS [DayMonthYear],

      Year(Date($(vMinDate) + RecNo() - 1)) as [Year],

      Month(Date($(vMinDate) + RecNo() - 1)) as [Month],

      Date(monthStart(Date($(vMinDate) + RecNo() - 1)), 'MMM-YYYY') AS [MonthYear],

          ceil(month(Date($(vMinDate) + RecNo() - 1))/3) as Quarter,

         'Q'&ceil(month(Date($(vMinDate) + RecNo() - 1))/3)&'-'&Year(Date($(vMinDate) + RecNo() - 1)) As QuarterYear

       

       

      AUTOGENERATE ($(vMaxDate) - $(vMinDate)+1);

       

       

      DROP TABLE Temp;

      DROP TABLE MinMax;

       

       

       

       

      Exit script;

        • Re: Master Calendar not matching actual data
          Aehman K

          trim(date([F10],'YYYYMMDD')) as [DateID],    For your Fact table                               


          Temp: Date($(vMinDate) + IterNo() - 1) as TempDate  For your Temp Table



          Master Table:


          trim(date(TempDate,'YYYYMMDD')) as [DateID],


          Both the date ID fields would link eachother.



          Look at attached Master Calendar script. It is bit advanced where flags are also created.

          Please check the Min and Max date (those might be different from what you need) also check the date format.

          • Re: Master Calendar not matching actual data
            Justin Dallas

            Where you have

             

            Temp:
            Load Date#([DayMonthYear]) as NumericDate
            

             

            Try replacing it with:

             

            Temp:
            Load DayStart(Date#([DayMonthYear])) as NumericDate
            

             

            I had a similar issue where I had MM/DD/YYYY, but it was as if there were hidden hours and minutes.  This of course lead to my calendar not linking up to anything at all.

             

            OH, and make sure the %calKey field on your FactTable is also DayStarted too.