Capturing Historical Snapshots of Projected Data

[Data_Derek]

I'm looking to capture and store historical data of projected employee schedules in my Qlik Sense application. My goal is to analyze how the projected headcount for a future date changes over time as that date approaches.

My Qlik Sense application currently shows a dynamic, two-week schedule. The values change daily with each reload due to new data (e.g., last-minute sick days, vacation approvals, etc.). This daily refresh means I only have the most current projection, and I lose the historical values that were visible on previous days.

I need a way to preserve a snapshot of the daily projection so I can compare "what was projected" with "what is currently projected" for any given date.

Example:

Let's say I'm tracking the projected number of employees scheduled to work on a specific date, say August 25th.

• On August 22nd, the app showed a projection of 128 employees for August 25th.

• On August 23rd, a new projection was loaded. It now shows 126 employees for August 25th.

• On August 24th, the projection updates again, showing 122 employees.

• On August 25th, the actual number is loaded, which is 120 employees.

I want to be able to see this progression:

| Date of Observation | Projected Date | Projected Value |

| August 22nd | August 25th | 128 |

| August 23rd | August 25th | 126 |

| August 24th | August 25th | 122 |

| August 25th | August 25th | 120 |

This historical log would allow me to analyze the change and visualize how projections evolve.

I'm open to any ideas on how to implement this. My primary questions are:

1. What's the best way to store this daily snapshot? Should I use a QVD to incrementally store the data?

2. How would I structure the Qlik Sense script to load the current day's projection, tag it with the "date of observation" (the reload date), and append it to an existing QVD file?

3. Are there any best practices or alternative methods for capturing this type of "slowly changing" historical data in Qlik Sense?

Any guidance or example scripts would be greatly appreciated!

[howdash · Accepted solution]

Here's what I've been doing and what I've learned through my career when handling these scenarios.

01. Best Way to Store Daily Snapshot

QVD Size

Storing snapshots in QVDs is what I have been doing in the same/similar scenarios. Doing that has been working well for me. Things to consider is the fact that as more days pass, the QVD will get larger and larger. So, depending on how much data you're storing every day you may or may not need to come up with a decision and a method for trimming irrelevant data.

If you're storing 200 records a day, which would be about 70,000 records in a year, you can store daily records for then next 20 years and it won't be a problem.

If you have 10,000 records recorded daily, I'd recommend developing some sort of a logic in the script that would trim older records, if you don't need them. Maybe store older records in separate QVD for archiving if you need those older records.

Main point though is to be aware of the growth of QVD size on a daily basis and come up with a way to trim the data if it gets too big and starts impacting app's performance.

Incremental Load

The idea of an incremental load is good. Incremental load is essentially a loop where you:

• Calculate today's projections.
• Load historically captured projections from previously created QVD(s).
• Add today's projections to historic projections.
• Store historic projections with today's projections in a QVD.
• Repeat same loop tomorrow.

This is what I've been doing and it has been working well for me. I recommend it.

02. How to Structure Qlik Sense Script

You will have to have some sort of a field that will identify each day's data. And you will need to think about how you want to handle:

• Multiple reloads in a day.
• Projection calculation logic changes.
• Growing QVD size.

The answers will impact how you'll build the incremental load script.

Here's a basic script that will give you an idea for how to capture daily records, combine today's projections with historic ones, and handle multiple reloads:

// load projections for today
projections:
Load *,
	Date(Today())					as [Date of Observation]
Inline [
	Projected Date|Projected Value
    August 25, 2025|128
] (delimiter is '|')
;

// if the QVD hasn't been created yet, create an empty table
// this is applicable only for the very first load
If IsNull(FileSize('lib://howdash/Qlik Community/historicProjections.qvd')) then

	historicProjections:
    NoConcatenate
    Load Null()			as [Date of Observation],
    	Null()			as [Projected Date],
        Null()			as [Projected Value]
    AutoGenerate 0
    ;

// otherwise, load previously stored/historic projectsions
// the WHERE statement exclude today's rpojections if they were already loaded
// this allows for multiple reloads in a day without duplication of today's data
Else

	historicProjections:
    NoConcatenate
    LOAD [Date of Observation],
        [Projected Date],
        [Projected Value]
    FROM [lib://howdash/Qlik Community/historicProjections.qvd]
    (qvd)
    Where [Date of Observation] <> Today()
    ;

End;

// add today's projections to historic projections
Concatenate(historicProjections)
Load [Date of Observation],
	[Projected Date],
	[Projected Value]
Resident projections
;

// store historic projections with today's projections included
Store * from historicProjections into 'lib://howdash/Qlik Community/historicProjections.qvd' (qvd);

// drop historic projections if they're not needed in the data model
Drop Table historicProjections;

03. Best Practices or Alternative Methods

These might not be global best practices, but these are the ones that I've developed over years and use regularly:

1. Design a reliable logic that will ALWAYS accurately load data.

You want to make sure that:

• None of the old projections get lost.
• New projections will get added every day.
• New projections won't get duplicated if an extra reload is executed in a day.

Things like that. It takes some effort and thinking and time to make sure that the data in the QVD is reliably maintained, but once you nail that logic, you'll have yourself the historic, daily snapshots that you're looking for being generated reliably and regularly.

2. Think ahead and discuss with the users/stakeholders of the app what historic data they want to analyze.

How much detail do they want to be able to see? For example, do they only care that today's projection is 120 and three days ago it was 128? Or do they want to know exactly which employees scheduled time off and which employees cancelled time off that they have scheduled?

Those things are important because if you don't record some data and then need it later, there's no way to recover it. That may be ok. It will happen. But it's worth to run that by the stakeholder/owner of the app and explain that to them so it wouldn't be a surprise to them. You will always be able to add new data to the incremental load, but you likely won't be able to recreate missing, historic data.

3. Decide on how you want to store historic data.

There are two common (in my experience) ways to store historic data:

• In a single QVD file.
• In daily QVD files.

A single QVD file is nice because it's faster to load and the script to load it is simpler. However, if that single QVD that holds all historic data gets overwritten due to some bad reload, all your historic data might be lost.

Having multiple QVDs, one for each day, makes the script to load all of them more complicated and the load time will be impacted, but there's less risk of historic QVDs being overwritten. The idea here is to have a QVD file named something like historicProjections_2025_08_20.qvd and historicProjections_2025-08-21.qvd and so on. Record each day's projections in its own QVD file. This way when you generate new day's projections, they'll get stored into their own QVD file and you won't have to worry about prior projections being overwritten.

I like the multiple QVDs option because of the reduced risk of losing historic data, but again, it's a bit more difficult to write a script to load them all.

Hope this helps and good luck!