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;
