Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

clausbarlose
New Contributor II

Help autogenerating dates

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

or

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

5 Replies
YoussefBelloum
Esteemed Contributor

Re: Help autogenerating dates

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 ?

clausbarlose
New Contributor II

Re: Help autogenerating dates

Hi Youssef

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

Br,

Claus

clausbarlose
New Contributor II

Re: Help autogenerating dates

Obviously impossible to attach files. Please find sample data below:

     

IDStateCreatedDateResponseDateResolutionDate
I-48393Closed01-08-201701-08-201701-08-2017
I-48396Closed01-08-201701-08-201701-08-2017
I-48400Closed01-08-201701-08-201701-08-2017
I-48426Closed01-08-201701-08-201701-08-2017
I-48456Closed01-08-201703-08-201720-02-2018
I-48518Closed02-08-201702-08-201707-08-2017
I-48556Closed03-08-201703-08-201703-08-2017
I-48594Closed03-08-201703-08-201721-11-2017
I-48642Closed04-08-201704-08-201707-08-2017
I-48682Closed04-08-201704-08-201719-09-2017
I-48693Closed04-08-201704-08-201709-11-2017
I-48702Closed04-08-201704-08-201722-09-2017
I-48737Closed07-08-201707-08-201707-08-2017
I-48750Closed07-08-201707-08-201710-08-2017
I-48759Closed07-08-201707-08-201721-08-2017
I-48807Closed07-08-201707-08-201715-11-2017
I-48831Closed07-08-201707-08-201721-08-2017
I-48853Closed07-08-201707-08-201714-08-2017
I-48929Closed08-08-201708-08-201709-08-2017
I-48958Closed08-08-201708-08-201709-08-2017
I-48997Active08-08-201708-08-2017
I-50175Waiting17-08-201717-08-2017
I-50447Active18-08-201718-08-2017
I-50716Active22-08-201722-08-2017
I-50852Active23-08-201723-08-2017
I-51135Waiting25-08-201725-08-2017
I-51130Closed25-08-201725-08-201706-11-2017
I-51144Closed25-08-201725-08-201704-09-2017
I-51151Closed25-08-201725-08-201725-08-2017
I-51168Closed25-08-201725-08-201725-08-2017
I-51173Closed25-08-201725-08-201728-11-2017
I-51180Closed25-08-201725-08-201725-09-2017
I-51186Active25-08-201725-08-2017
I-51203Closed25-08-201725-08-201714-12-2017
I-51241Closed28-08-201728-08-201728-11-2017
I-51255Active28-08-201728-08-2017
I-51296Closed28-08-201728-08-201705-01-2018
I-51300Closed28-08-201728-08-201703-11-2017
I-51309Closed28-08-201728-08-201712-09-2017
I-51331Closed28-08-201728-08-201712-09-2017
I-51336Closed28-08-201728-08-201704-10-2017
I-51377Closed29-08-201729-08-201711-09-2017

Br,

Claus

YoussefBelloum
Esteemed Contributor

Re: Help autogenerating dates

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

clausbarlose
New Contributor II

Re: Help autogenerating dates

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

Community Browser