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:
Tickets Open at Month End
My actual data as it is imported looks like this, and the status is the current status of each ticket.
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?
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.