topic Re: Master Calendar in Qlik in App Development

Master Calendar in Qlik

Mouni09 — Thu, 13 Feb 2025 15:36:19 GMT

Hello All,
I was trying to build master calendar in Qlik based on Sales Date column in Sales table, but I am getting only 2021 may 5th date for whole calendar table but I am having dates till 2025 in Sales date, please help me if I am doing anything wrong in script

// Find Min and Max Date from Sales Table
TempMinMax:
LOAD
Min("Sales Date") AS MinDate,
Max("Sales Date") AS MaxDate
RESIDENT Sales;

// Generate a continuous date range
LET vMinDate = Peek('MinDate', 0, 'TempMinMax');
LET vMaxDate = Peek('MaxDate', 0, 'TempMinMax');

// Create Master Calendar
MasterCalendar:
LOAD
Date($(vMinDate) + IterNo() - 1) AS "Sales Date",
Year($(vMinDate) + IterNo() - 1) AS Year,
Month($(vMinDate) + IterNo() - 1) AS Month,
'Q' & Ceil(Month($(vMinDate) + IterNo() - 1) / 3) AS Quarter,
Week($(vMinDate) + IterNo() - 1) AS Week,
Day($(vMinDate) + IterNo() - 1) AS Day,
MonthName($(vMinDate) + IterNo() - 1) AS MonthYear
AUTOGENERATE $(vMaxDate) - $(vMinDate) + 1;

// Drop temporary table
DROP TABLE TempMinMax;

and below is how date from sales date looks like

Re: Master Calendar in Qlik

Vegar — Thu, 13 Feb 2025 15:56:43 GMT

Replace your 'iterno()' with 'recno()' and your calendar should be fine.

(Or maybe you need to replace it with 'rowno()')

Re: Master Calendar in Qlik

morgankejerhag — Sat, 15 Feb 2025 11:03:08 GMT

The Sales Dates are in text format (left aligned). You want to convert them to date format in the transaction table first. In the load statement reading the transactions:

date#(SalesDate,'M/D/YYYY') as SalesDate

Re: Master Calendar in Qlik

mikaelsc — Sat, 15 Feb 2025 19:10:08 GMT

not sure iterno() increments without while (or other loop "statement"):

AUTOGENERATE (1)

while $(vMinDate) +iterno()-1 <=$(vMaxDate);

Re: Master Calendar in Qlik

diegozecchini — Sun, 16 Feb 2025 07:51:00 GMT

Logic seeams correct .. Did you check that Sales Date is being interpreted correctly? You can try converting it explicitly:

TempMinMax:
LOAD
Min(Date#("Sales Date", 'M/D/YYYY')) AS MinDate,
Max(Date#("Sales Date", 'M/D/YYYY')) AS MaxDate
RESIDENT Sales;

and check in the script execution log

TRACE vMinDate = $(vMinDate);
TRACE vMaxDate = $(vMaxDate);