More than a quarter of organizations lose over 5 million dollars a year to poor data quality, according to the IBM Institute for Business Value (2025). And one of the most frequent causes is also the most underestimated: every area calculates time its own way. In QlikSense, the master calendar is the modeling piece that removes that problem at the root. It is humble, it never shows up in a sales demo, and yet it decides whether your time analysis is trustworthy or decorative.

Sales closes by calendar month. Finance, by fiscal period. Operations, by week. When the three arrive at the committee with different figures for «May», the meeting does not decide: it reconciles. And a company that meets to reconcile numbers is a company paying managers to do arithmetic.
Here is the complete script, commented line by line, so you never have to live through that scene again.
The real problem: three versions of «this month»
The symptom is familiar: two dashboards show different YTD sales. The cause is almost never the data; it is that each developer calculated time in their own expression. One used Year(Fecha)=2026, another compared against the load date, a third forgot that the fiscal year starts in July.
The cost of that inconsistency is not technical, it is political: when a manager spots two different numbers for the same question, he stops trusting both. And trust in a BI system is lost only once. Rebuilding it costs years, budget and the credibility of the IT team.

QlikSense master calendar: the complete script
The logic has two steps: first we detect the actual date range of the fact table; then we generate one row for each day in that range, with every derived field and flag. No hand-typed dates.
Step 1: the date range calculates itself
// 1) Detect the actual date range from the fact table
TempRango:
LOAD
Min(Fecha) AS FechaMin,
Max(Fecha) AS FechaMax
RESIDENT Ventas;
LET vFechaMin = Peek('FechaMin', 0, 'TempRango');
LET vFechaMax = Peek('FechaMax', 0, 'TempRango');
DROP TABLE TempRango;Peek() reads the value from the first row of the temporary table and stores it in a variable. With this, the calendar grows on its own as new data comes in. Zero maintenance, zero hardcoded dates.
Step 2: generating the calendar with flags
// 2) Generate one row per day, with derived fields and flags
CalendarioMaestro:
LOAD
TempFecha AS Fecha,
Year(TempFecha) AS Año,
Month(TempFecha) AS Mes,
Date(MonthStart(TempFecha), 'MMM-YYYY') AS MesAño,
'T' & Ceil(Month(TempFecha)/3) AS Trimestre,
Week(TempFecha) AS Semana,
WeekDay(TempFecha) AS DiaSemana,
// Fiscal year starting in July (adjust the 7 to your case)
If(Month(TempFecha) >= 7,
Year(TempFecha) + 1, Year(TempFecha)) AS AñoFiscal,
// Period flags: the jewel of the model
If(TempFecha >= YearStart(Today())
AND TempFecha <= Today(), 1, 0) AS _FlagYTD,
If(TempFecha >= MonthStart(Today())
AND TempFecha <= Today(), 1, 0) AS _FlagMTD,
If(TempFecha >= AddYears(YearStart(Today()), -1)
AND TempFecha <= AddYears(Today(), -1), 1, 0) AS _FlagYTD_LY;
// Generator: one row for each day in the detected range
LOAD
Date($(vFechaMin) + IterNo() - 1) AS TempFecha
AUTOGENERATE 1
WHILE $(vFechaMin) + IterNo() - 1 <= $(vFechaMax);The lower block is a preceding LOAD with AUTOGENERATE: IterNo() iterates day by day from the minimum date to the maximum, without depending on the fact table having sales every day. That guarantees a continuous calendar with no gaps. The upper block derives every field from that date, once, in a single place.
Flags that turn Set Analysis into simple sentences
Here is the return on the investment. Without flags, a YTD against the prior year requires expressions with dates, variables and parentheses that only their author understands. With flags, it looks like this:
// YTD sales
Sum({<_FlagYTD = {1}>} Ventas)
// YTD growth against the same period last year
Sum({<_FlagYTD = {1}>} Ventas)
/ Sum({<_FlagYTD_LY = {1}>} Ventas) - 1Any developer on the team understands that expression in three seconds. Comparing against a numeric flag is also noticeably faster than evaluating date functions in every cell of every chart. In models with tens of millions of rows, that difference shows up in every click.
The same pattern extends to any period your business actually uses. Need the rolling quarter, the last 13 weeks or the comparison against the same month last year? You add one more flag to the script and every dashboard inherits it instantly:
// Rolling last 12 months (L12M window)
If(TempFecha > AddMonths(Today(), -12)
AND TempFecha <= Today(), 1, 0) AS _FlagL12M,
// Same month last year (for year-over-year MoM comparisons)
If(TempFecha >= AddYears(MonthStart(Today()), -1)
AND TempFecha <= AddYears(Today(), -1), 1, 0) AS _FlagMTD_LYEach new flag costs two lines of script. Every expression that uses it becomes readable, fast and, above all, identical across the whole company. That is the economy of scale of good modeling.

Fiscal year without the pain
The AñoFiscal line in the script deserves attention. If your fiscal year starts in July, everything happening from July to December belongs to the following fiscal year. Change the 7 for the real starting month of your fiscal year and add, if you need it, the fiscal quarter with the same logic. What matters is that the rule lives in the model, not in the accountant's head nor repeated across thirty expressions.
Business impact: a single version of time
First, consistency: every dashboard in the company inherits the same definition of YTD, MTD and fiscal year. Meetings go back to being about decisions. Second, speed: numeric flags accelerate both the expressions and the development; a comparative KPI that used to take an afternoon of trial and error now takes minutes. Third, team scalability: a new developer understands the model on day one, because time is solved in a single documented script.
And there is a fourth effect, less visible: the quality of the management conversation rises. When nobody argues about the denominator, the discussion moves to the business. That is the real deliverable of analytics, as I argue in hectorpincheira.com: it is not the charts, it is the quality of the decisions.
Common mistakes when building the calendar
Best practices from experience
Keep a single master calendar per model and store it as a reusable QVD for the whole company. Use the _Flag prefix on every period indicator: it makes the model self-explanatory. Document in the script itself the starting month of the fiscal year and the canonical date you chose. Finally, validate after every load that the day count matches the range: a calendar with gaps is a silent bug that produces plausible, false figures.
One extra piece of advice I learned the hard way: treat the calendar as a corporate asset, not as a detail of each application. When there is a single calendar QVD and every app consumes it, any change of rule —a new fiscal year start, a holiday that affects the plant, an accounting close week— is fixed in one place and propagates to the whole company on the next load. That centralization is, in miniature, what governing data really means.
Strategic conclusion
Companies do not argue for lack of data. They argue because each area has its own version of time. The master calendar is the architecture decision that ends that argument for good, and it costs exactly one script.
The next time two managers arrive at the committee with different figures for «this month», do not look for the error in the data. Look for it in the model. Most likely, time in your company is still an opinion. Turn it into a definition.




