Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hoping for help on this one:
I am doing statistics on support tickets and whats to track how many tickets are open on a given date from when a ticket is opened until it is solved.
A somewhat smaller version of the ticket-table is like this:
I’ve come so far as to understand that I have to generate a separate table, where I autogenerate dates between CreatedDate and ResolutionDate and add counter to keep track on how many active tickets there are on a given date, but I am having severe trouble getting it together.
The resulting table I think looks like this:
or
- both with the purpose of counting number of active tickets, active being defined on a given date if not resolved.
Any good advice on this?
Hi,
your problem here is pretty simple, because you have all your different dates on the same table, on the same line.
there is aggregation functions on Qlik to help you calculate your measures (number of active tickets etc etc...), so no need for a counter.
the most important here is to create a master calendar using the minimum CreatedDate as a start date and a maximum lastest createddate as End date, to be able to analyse your information using time axis (because most of your dimensions are temporal)
can you attach some data from your tickets table to try to help you more ?
Hi Youssef
Sounds nice! - I'll attach some sample data, when I find out how
Br,
Claus
Obviously impossible to attach files. Please find sample data below:
ID | State | CreatedDate | ResponseDate | ResolutionDate |
I-48393 | Closed | 01-08-2017 | 01-08-2017 | 01-08-2017 |
I-48396 | Closed | 01-08-2017 | 01-08-2017 | 01-08-2017 |
I-48400 | Closed | 01-08-2017 | 01-08-2017 | 01-08-2017 |
I-48426 | Closed | 01-08-2017 | 01-08-2017 | 01-08-2017 |
I-48456 | Closed | 01-08-2017 | 03-08-2017 | 20-02-2018 |
I-48518 | Closed | 02-08-2017 | 02-08-2017 | 07-08-2017 |
I-48556 | Closed | 03-08-2017 | 03-08-2017 | 03-08-2017 |
I-48594 | Closed | 03-08-2017 | 03-08-2017 | 21-11-2017 |
I-48642 | Closed | 04-08-2017 | 04-08-2017 | 07-08-2017 |
I-48682 | Closed | 04-08-2017 | 04-08-2017 | 19-09-2017 |
I-48693 | Closed | 04-08-2017 | 04-08-2017 | 09-11-2017 |
I-48702 | Closed | 04-08-2017 | 04-08-2017 | 22-09-2017 |
I-48737 | Closed | 07-08-2017 | 07-08-2017 | 07-08-2017 |
I-48750 | Closed | 07-08-2017 | 07-08-2017 | 10-08-2017 |
I-48759 | Closed | 07-08-2017 | 07-08-2017 | 21-08-2017 |
I-48807 | Closed | 07-08-2017 | 07-08-2017 | 15-11-2017 |
I-48831 | Closed | 07-08-2017 | 07-08-2017 | 21-08-2017 |
I-48853 | Closed | 07-08-2017 | 07-08-2017 | 14-08-2017 |
I-48929 | Closed | 08-08-2017 | 08-08-2017 | 09-08-2017 |
I-48958 | Closed | 08-08-2017 | 08-08-2017 | 09-08-2017 |
I-48997 | Active | 08-08-2017 | 08-08-2017 | |
I-50175 | Waiting | 17-08-2017 | 17-08-2017 | |
I-50447 | Active | 18-08-2017 | 18-08-2017 | |
I-50716 | Active | 22-08-2017 | 22-08-2017 | |
I-50852 | Active | 23-08-2017 | 23-08-2017 | |
I-51135 | Waiting | 25-08-2017 | 25-08-2017 | |
I-51130 | Closed | 25-08-2017 | 25-08-2017 | 06-11-2017 |
I-51144 | Closed | 25-08-2017 | 25-08-2017 | 04-09-2017 |
I-51151 | Closed | 25-08-2017 | 25-08-2017 | 25-08-2017 |
I-51168 | Closed | 25-08-2017 | 25-08-2017 | 25-08-2017 |
I-51173 | Closed | 25-08-2017 | 25-08-2017 | 28-11-2017 |
I-51180 | Closed | 25-08-2017 | 25-08-2017 | 25-09-2017 |
I-51186 | Active | 25-08-2017 | 25-08-2017 | |
I-51203 | Closed | 25-08-2017 | 25-08-2017 | 14-12-2017 |
I-51241 | Closed | 28-08-2017 | 28-08-2017 | 28-11-2017 |
I-51255 | Active | 28-08-2017 | 28-08-2017 | |
I-51296 | Closed | 28-08-2017 | 28-08-2017 | 05-01-2018 |
I-51300 | Closed | 28-08-2017 | 28-08-2017 | 03-11-2017 |
I-51309 | Closed | 28-08-2017 | 28-08-2017 | 12-09-2017 |
I-51331 | Closed | 28-08-2017 | 28-08-2017 | 12-09-2017 |
I-51336 | Closed | 28-08-2017 | 28-08-2017 | 04-10-2017 |
I-51377 | Closed | 29-08-2017 | 29-08-2017 | 11-09-2017 |
Br,
Claus
Take a look at the app attached here and tell me:
No, thats not it. If a ticket is or was open at the day in question, CreationDate is less than or equal to the day and ResolutionDate is equal to or greather than the day.
So - a set analysis with to compares.
I've done a manual check in Excel with the following dates:
1/8-2017 - 5 tickets were open
2/8-2017 - 2 tickets were open
3/8-2017 - 4 tickets were open
4/8-2017 - 7 tickets were open:
I-48456
I-48518
I-48594
I-48642
I-48682
I-48693
I-48702