Master Calendar not matching actual data

[malradi88]

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;

[MK9885]

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.

[JustinDallas]

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.