How can I set the ticket age including all ticket status?

Answered

7 Comments

  • Marie-Cathrine Sørensen
    Community Moderator

    Hi Irene,

    You can get ticket age for all statuses by creating this calculated metric:

    IF ([Ticket status] = "Solved" OR [Ticket status] = "Closed")
    THEN DATE_DIFF([Ticket solved - Timestamp], [Ticket created - Timestamp], "nb_of_days")
    ELIF ([Ticket status] != "Solved" AND [Ticket status] != "Closed")
    THEN DATE_DIFF(NOW(), [Ticket created - Timestamp], "nb_of_days")
    ENDIF

    #helpsome regards,
    Marie-Cathrine Sørensen
    Business Intelligence Analyst @ helphouse.io

    0
  • Irene Yung

    Hi Marie-Cathrine Sørensen,

    Thanks for the help!

    When the average become lower than the one exclude solved+closed, is it normal?

     

    0
  • Marie-Cathrine Sørensen
    Community Moderator

    It is normal. The average ticket age will change and go up or down depending on what statuses you have included or excluded in filters and attributes in the query.

    0
  • Irene Yung

    Hi Marie-Cathrine Sørensen

    Having another question. I'd like to set up "Ticket age brackets" (includes all ticket status) and the brackets options similar with Unsolved Ticket age brackets: 1 day, 2-7 days, 8-30 days, 31-60 days, 61-90 days, >90 days"

    How could I set up?

    Also, the default "Unsolved Ticket age brackets" are 1 day, 1-7 days, 7-30 days, >30 days. Would the days on 1,7,30 be overlapped? Can you explain if is it normal industrial counting?

    Thanks a lot!

    0
  • Marie-Cathrine Sørensen
    Community Moderator

    Hi Irene,

    I don't have an answer right now as to how to set up the brackets, but if you want, I can investigate this and come back with an answer to whether it possible or not, and if it is, how to set it up?

    And regarding the already existing brackets, 1, 7 and 30 are only counted once: 1 is included in the 1 day bracket, 7 in the 1-7 days and 30 in the 7-30 days.

    #helpsome regards,
    Marie-Cathrine Sørensen
    Business Intelligence Analyst @ helphouse.io

    0
  • Lohith S

    Hi Marie,

    How do we set up the Ticket Age < 30, Between 30 - 60, Between 60 - 90 and > 90 days ?

    In MAQLI have set this up using below query

    select # Unsolved Tickets where( select Ticket Age (days) [AVG] by Ticket Id ) < 30

    How do I set it up in Explore ?

    Thanks

    0
  • Marie-Cathrine Sørensen
    Community Moderator

    Hi Lohith,

    There are two ways to approach this one: You can create a standard calculated metric for each bracket or create a standard calculated attribute that divides your unsolved tickets into brackets. Both are using the Tickets dataset.

    Using calculated metric

    1. Go to Calculations > Standard calculated metric.
    2. Create 4 metrics using the following formulas:

      Ticket age <30 days
      IF (VALUE(Unsolved tickets age (days)) < 30) 
      THEN [Ticket ID]
      ENDIF
      Ticket age 30-60 days
      IF (VALUE(Unsolved tickets age (days)) >= 30 
      AND VALUE(Unsolved tickets age (days)) <= 60)
      THEN [Ticket ID]
      ENDIF
      Ticket age 60-90 days
      IF (VALUE(Unsolved tickets age (days)) > 60 
      AND VALUE(Unsolved tickets age (days)) <= 90)
      THEN [Ticket ID]
      ENDIF
      Ticket age >90 days
      IF (VALUE(Unsolved tickets age (days)) > 90) 
      THEN [Ticket ID]
      ENDIF
    3. Add the new Standard calculated metrics as Metrics using the aggregator COUNT.

    Using calculated attribute

    1. Add Unsolved tickets as a Metric.
    2. Go to Calculations > Standard calculated attribute.
    3. Add the following formula:
      IF (VALUE(Unsolved tickets age (days)) < 30) 
      THEN "<30 days"
      ELIF (VALUE(Unsolved tickets age (days)) >= 30
      AND VALUE(Unsolved tickets age (days)) <= 60)
      THEN "30-60 days"
      ELIF (VALUE(Unsolved tickets age (days)) > 60
      AND VALUE(Unsolved tickets age (days)) <= 90)
      THEN "60-90 days"
      ELIF (VALUE(Unsolved tickets age (days)) > 90)
      THEN ">90 days"
      ELSE "Other"
      ENDIF
    4. Select the new attribute as either a Row or Column depending on what kind of visualization you want.

    I hope this helps :)

    #helpsome regards,
    Marie-Cathrine Sørensen
    Business Intelligence Specialist & Junior Developer @ helphouse.io

    0

Please sign in to leave a comment.

Powered by Zendesk