topic Re: Help autogenerating dates in QlikView

Help autogenerating dates

clausbarlose — Tue, 27 Feb 2018 20:36:04 GMT

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:

TicketID
CreatedDate
ResponseDate
ResolutionDate

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:

Date
Counter

Date
TicketID

- 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?

Re: Help autogenerating dates

YoussefBelloum — Tue, 27 Feb 2018 23:22:19 GMT

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 ?

Re: Help autogenerating dates

clausbarlose — Wed, 28 Feb 2018 16:01:13 GMT

Hi Youssef

Sounds nice! - I'll attach some sample data, when I find out how

Br,

Claus

Re: Help autogenerating dates

clausbarlose — Wed, 28 Feb 2018 16:07:57 GMT

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

Re: Help autogenerating dates

YoussefBelloum — Wed, 28 Feb 2018 16:31:47 GMT

Take a look at the app attached here and tell me:

Re: Help autogenerating dates

clausbarlose — Wed, 28 Feb 2018 19:15:33 GMT

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