Qlik Snapshot in Time

[quickqlik2]

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?

[tm_burgers]

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 !