klarmetrics

Find the money hiding in your data

Home / Learning Paths & Courses
LEARNING PATHS & COURSES

Temporal Data in Qlik Sense: IntervalMatch & Master Calendar Complete Guide

KlarMetrics

October 6, 2025 · 6 min read

This is Article 14 of the Qlik Sense Data Modeling Course.

Qlik Sense Course – Article 14 of 28

← Previous Article: SCD2 Implementation – Tracking Historical Data
→ Next Article: Building a Master Calendar

What is IntervalMatch? IntervalMatch is a special Qlik function that automatically maps individual points in time (e.g., sale date) to time periods (e.g., budget periods) – without complex JOINs. For the full function reference, see the IntervalMatch function reference.

What Will You Learn About Temporal Data, IntervalMatch & Master Calendar?

After this guide, you will be able to:

  1. Map events to time periods using IntervalMatch
  2. Create a Master Calendar for gapless time-series analysis
  3. Use the Date Island pattern for flexible calendar-based analysis

Time investment: 20 min reading + 3 hrs hands-on
Prerequisites: Basic knowledge of LOAD strategies
Quick Win: In 15 minutes you will have your first IntervalMatch implemented

What Is the Problem with Temporal Data?

The Problem: You have sales with exact timestamps (OrderDate) and budgets with time periods (Start/End). How do you assign each sale to the correct budget period?

The Solution: IntervalMatch handles this automatically! It checks for each point in time whether it falls within one or more time periods – performance-optimized and without complex WHERE clauses.

How Does IntervalMatch Work with Temporal Data?

What Is the Core Concept of IntervalMatch?

Imagine:

  • Sales: Individual points in time (Feb 15, 2023, May 20, 2023, …)
  • Budget: Time periods (Q1 2023: Jan 1 – Mar 31, Q2 2023: Apr 1 – Jun 30)

IntervalMatch automatically creates the connection: “Sale from Feb 15 falls within the Q1 budget period”

How Do I Compare Sales with Budget Periods Using IntervalMatch?

// Sales (Point-in-Time)
Facts_Sales:
LOAD
    OrderID,
    CustomerID,
    ProductID,
    Date(OrderDate) as OrderDate,
    Amount
FROM [DataSales.xlsx]
(ooxml, embedded labels);

Explanation: These are our sales – each has an exact OrderDate.

// Budget (Time Periods)
Temp_Budget:
LOAD
    BudgetID,
    ProductID,
    Date(PeriodStart) as PeriodStart,
    Date(PeriodEnd) as PeriodEnd,
    BudgetAmount
FROM [DataBudget.xlsx]
(ooxml, embedded labels);

Explanation: The budget applies to specific time periods (start to end).

// IntervalMatch: Connect OrderDate with Budget Periods
IntervalMatch(OrderDate, ProductID)
LOAD DISTINCT
    PeriodStart,
    PeriodEnd,
    ProductID
RESIDENT Temp_Budget;

Explanation: IntervalMatch checks: Does OrderDate fall between PeriodStart and PeriodEnd? If yes, create the connection. The second field (ProductID) ensures that only budgets for the same product are compared.

// Add budget attributes to Sales
LEFT JOIN (Facts_Sales)
LOAD
    ProductID,
    PeriodStart,
    PeriodEnd,
    BudgetID,
    BudgetAmount
RESIDENT Temp_Budget;

Explanation: Now we attach the budget details to the Sales table.

// Cleanup: Remove interval fields
Facts_Sales_Clean:
LOAD
    OrderID,
    CustomerID,
    ProductID,
    OrderDate,
    Amount,
    BudgetID,
    BudgetAmount
RESIDENT Facts_Sales;

DROP TABLES Temp_Budget, Facts_Sales;
RENAME TABLE Facts_Sales_Clean to Facts_Sales;

Explanation: We no longer need PeriodStart and PeriodEnd – clean them up!

Performance Tip: IntervalMatch is 3x faster than a JOIN with WHERE clause! With 100k events and 500 intervals: 8 seconds instead of 25 seconds.

How Can I Link User Events to Sessions in Example 2?

A classic scenario: you have individual user clicks and want to know which session they belong to.

// User Events (Clicks)
Facts_Events:
LOAD
    EventID,
    UserID,
    Timestamp(EventTimestamp) as EventTimestamp,
    EventType
FROM [DataUserEvents.csv]
(txt, utf8, embedded labels);

Explanation: Each click has a precise timestamp.

// Sessions (Start/End per User)
Temp_Sessions:
LOAD
    SessionID,
    UserID,
    Timestamp(SessionStart) as SessionStart,
    Timestamp(SessionEnd) as SessionEnd,
    DeviceType
FROM [DataSessions.csv]
(txt, utf8, embedded labels);

// IntervalMatch: Event to Session (per User!)
IntervalMatch(EventTimestamp, UserID)
LOAD DISTINCT
    SessionStart,
    SessionEnd,
    UserID
RESIDENT Temp_Sessions;

Explanation: UserID is critical here! Otherwise, events from User A would incorrectly be assigned to sessions from User B.

// Session attributes to Events
LEFT JOIN (Facts_Events)
LOAD
    UserID,
    SessionStart,
    SessionEnd,
    SessionID,
    DeviceType
RESIDENT Temp_Sessions;

// Cleanup
Facts_Events_Clean:
LOAD
    EventID,
    UserID,
    EventTimestamp,
    EventType,
    SessionID,
    DeviceType
RESIDENT Facts_Events;

DROP TABLES Temp_Sessions, Facts_Events;
RENAME TABLE Facts_Events_Clean to Facts_Events;

Explanation: Done! Now we know which event belongs to which session.

How Do I Create Gapless Time Series with the Master Calendar?

The Problem: Not every day has transactions. When you run YTD analyses or show trends, data is missing for days without sales.

The Solution: A Master Calendar with ALL dates – no gaps. Plus pre-calculated dimensions (Year, Quarter, Month) for performance. For the complete implementation, see the dedicated master calendar implementation guide.

How Do I Create a Simple Master Calendar?

// Determine Min/Max Date
LET vMinDate = Num(MakeDate(2020, 1, 1));
LET vMaxDate = Num(Today() + 365);

// Generate temp calendar
Temp_Calendar:
LOAD
    Date($(vMinDate) + IterNo() - 1) as TempDate
AUTOGENERATE 1
WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Explanation: AUTOGENERATE + IterNo() + WHILE is the standard pattern. It generates one record per day between Min and Max Date.

// Calendar with attributes
Master_Calendar:
LOAD
    TempDate as Date,
    Year(TempDate) as Year,
    Month(TempDate) as Month,
    MonthName(TempDate) as MonthYear,
    Week(TempDate) as Week,
    WeekDay(TempDate) as WeekDay,
    If(WeekDay(TempDate) >= 1 AND WeekDay(TempDate) <= 5, 1, 0) as IsWorkDay,
    If(Month(TempDate) = Month(Today()), 1, 0) as IsCurrentMonth
RESIDENT Temp_Calendar
ORDER BY TempDate ASC;

DROP TABLE Temp_Calendar;

Explanation: Now we have Year, Month, Week, etc. pre-calculated for every day. No more runtime calculations needed! For all available Qlik date and time functions, see the official reference.

Performance Boost: With a Master Calendar, queries are 3x faster! Instead of calculating Year(OrderDate) on every query, you simply use the pre-calculated field.

How Do I Create a Master Calendar with Fiscal Year?

// Fiscal Year starts in April
LET vFiscalYearStartMonth = 4;

Master_Calendar:
LOAD
    TempDate as Date,

    // Standard Calendar
    Year(TempDate) as Year,
    Month(TempDate) as Month,

    // Fiscal Year
    If(Month(TempDate) >= $(vFiscalYearStartMonth),
       Year(TempDate),
       Year(TempDate) - 1) as FiscalYear,

    'FQ' & Ceil(Mod(Month(TempDate) - $(vFiscalYearStartMonth), 12) / 3 + 1) as FiscalQuarter
RESIDENT Temp_Calendar;

Explanation: March 2023 falls in Fiscal Year 2022 (because FY 2023 starts in April). The formula handles this automatically.

What Is the Date Island Pattern?

The Problem: You have multiple date fields in Facts (OrderDate, ShipDate, DueDate). If you link the calendar directly, Synthetic Keys are created.

The Solution: Date Island – the calendar stays UNLINKED. In the frontend, you link explicitly via Set Analysis.

How Do I Implement a Date Island?

// Facts with multiple date fields
Facts_Orders:
LOAD
    OrderID,
    Date(OrderDate) as OrderDate,
    Date(ShipDate) as ShipDate,
    Date(RequiredDate) as RequiredDate,
    Amount
FROM [DataOrders.xlsx]
(ooxml, embedded labels);

Explanation: Three date fields – normally a problem.

// Calendar (Date Island - NOT linked!)
Master_Calendar:
LOAD
    TempDate as CalendarDate,    // Different field name!
    Year(TempDate) as Year,
    Month(TempDate) as Month
RESIDENT Temp_Calendar;

Explanation: CalendarDate is NOT named OrderDate/ShipDate – no automatic association!

// In the frontend: Set Analysis
// Orders by Order Year:
Sum({} Amount)

// Orders by Ship Year:
Sum({} Amount)

Explanation: You decide in the frontend which date field to use. Flexible and without Synthetic Keys! This is closely related to how slowly changing dimensions handle historical date-based changes.

Troubleshooting: IntervalMatch Finds No Matches

Problem: After IntervalMatch, all fields are NULL.

Cause: Data type mismatch! Point-in-time is Date, interval is Timestamp → no matches.

Solution: Use consistent data types:

Date(Floor(OrderTimestamp)) as OrderDate,
Date(Floor(PeriodStart)) as PeriodStart
Troubleshooting: IntervalMatch Creates Too Many Records

Problem: Table explodes with millions of records.

Cause: Overlapping intervals without grouping.

Solution: Add more fields to IntervalMatch for grouping:

IntervalMatch(OrderDate, ProductID, Region)
LOAD DISTINCT
    PeriodStart,
    PeriodEnd,
    ProductID,
    Region
RESIDENT Budget;

How to use IntervalMatch with temporal data in Qlik Sense?

← Previous Article: SCD2 Implementation
→ Next Article: Building a Master Calendar

Previous: Slowly Changing Dimensions | Next: Master Calendar

What would help you most right now?

Thanks!