Skip to main content
The way to achieve your own success is the willingness to help somebody else. Go for it!
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Qlik Snapshot in Time

I want to create a table in Qlik Sense that counts the number of open tickets month to month from previous snapshots in time. I basically want to freeze the ticket status at the end of each month at midnight. The final "count" table would look like this:

Month Tickets Open at Month End
2021-02-01 1
2021-03-01 2
2021-04-01 1
2021-05-01 2
2021-06-01 1


My actual data as it is imported looks like this, and the status is the current status of each ticket.

TicketNumber StartDate EndDate Status
00001 2021-02-18 2021-02-20 Closed
00002 2021-02-18 2021-04-29 Closed
00003 2021-03-04 2021-06-11 Closed
00004 2021-05-04 2021-06-11 Closed
00005 2021-06-24 2021-06-29 Closed
00006 2021-06-24 2021-06-29 Closed
00007 2021-06-24 (Still Open) Open


How do I go back in time and snapshot the status of tickets, month to month, to be able to count them in the manner shown in the first table of this post?

Labels (1)
1 Reply
Creator III
Creator III

The way that I do it is if your App reloads daily, save to a QVD daily the current copy of the Table - then you will end up with a bunch of QVD's. 


ie. QVD_Ticket_2021_01_01.qvd; QVD_Ticket_2021_01_02.qvd... etc


let vToday = date(today(),'YYYY_MM_DD')

Store * from TicketData into 'lib://...../ QVD_Ticket_'$(vToday)'.qvd';


Then load up all these QVD's and use the end of the FileName as the report date - or use the FileTime() function.


You could also write logic into your load script that would only save the QVD if it is the last day of the month, and then you wouldn't have a daily file - depending on your needs, having daily snapshots may be helpful, but if it isn't then when you load up all the historical QVDs you can only pull the count of open for the last day of the month.


Another option is having one master QVD that stores the snapshots, and then you can Concatenate "today's" snapshot into that one file.  


Let me know if I can help clarify !