I am working on a Qlik Sense application to track and analyse tickets. These IT tickets keep track of all operations and support activities that are performed to keep the IT environment up and running.
In the attached Excel is a sample of the dataset we use and the fields we have in our data are:
ID, Location, Priority, Type, Creation Date, Title, Status, Last Modification Date, Closing Date, Category, Assigned to, Owner.
The objective is to create a report with a table that allows us to select the dimension of time we want to visualize (day, week or month) and show us the number of open tickets, closed tickets, the difference of tickets in that period (open - closed) and the accumulated tickets from the beginning until that moment.
| Day |
Open |
Closed |
Diff |
Acumulated |
| 22/12/2022 |
5 |
7 |
-2 |
141 |
| 21/12/2022 |
6 |
19 |
-13 |
143 |
| 20/12/2022 |
25 |
12 |
13 |
156 |
| 19/12/2022 |
24 |
2 |
22 |
143 |
| 18/12/2022 |
6 |
20 |
-14 |
121 |
| 17/12/2022 |
29 |
11 |
18 |
135 |
| 16/12/2022 |
11 |
19 |
-8 |
117 |
| 15/12/2022 |
26 |
18 |
8 |
125 |
| 14/12/2022 |
9 |
18 |
-9 |
117 |
| 13/12/2022 |
12 |
22 |
-10 |
126 |
| 12/12/2022 |
14 |
26 |
-12 |
136 |
| 11/12/2022 |
27 |
29 |
-2 |
148 |
| 10/12/2022 |
4 |
18 |
-14 |
150* |
*Cumulative difference from the start of the times to the corresponding date
Or
| Week |
Open |
Closed |
Diff |
Acumulated |
| 52 |
66 |
60 |
6 |
141 |
| 51 |
128 |
143 |
-15 |
135 |
| 50 |
4 |
18 |
-14 |
150 |
The problem is that I don't know how to work with the creation date and the closing date to be able to do all these calculations and work with a single date field that should be the dimension that is displayed to the table or filtered in the application.
Could you help me to know how to load the data or how to create the calendar to be able to work in the application and make selections with a single date field?
