5 Replies Latest reply: Feb 28, 2018 2:15 PM by Claus Barlose RSS

    Help autogenerating dates

    Claus Barlose

      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?

        • Re: Help autogenerating dates
          youssef belloum

          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
              Claus Barlose

              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

            • Re: Help autogenerating dates
              Claus Barlose

              Hi Youssef

               

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

              Br,

              Claus