Master Calendar in Qlik

[Mouni09]

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

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download

[Vegar · Accepted solution]

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

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

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download

[morgankejerhag]

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

[mikaelsc]

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

AUTOGENERATE (1)

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

[diegozecchini]

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);