# 9 Kommentare

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

Thanks for the help!

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

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.

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!

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

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

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

This answer seems incomplete to me because a tickets status can be opened again after it is closed.  This query would only show age for when the ticket was first closed unless you had it search for MAX(updated) with the status = 'closed' or status = 'solved'.

I would also suggest using a CASE statement instead of using nested IF statements for efficiency.

Example:

(
SELECT
t.id,
t.created_at,
t.status,
CASE WHEN t.status in ('closed', 'solved', 'deleted') THEN timestamp_diff(t.updated_at, t.created_at, day)
ELSE timestamp_diff(current_timestamp, t.created_at, day)
END AS ticket_age
FROM
staging_zendesk.ticket AS t

), enhanced_ticket AS
(
SELECT
t.*,
af.ticket_age,
CASE WHEN af.ticket_age BETWEEN 0 AND 30 THEN 0
WHEN af.ticket_age BETWEEN 31 AND 60 THEN 1
WHEN af.ticket_age BETWEEN 61 AND 90 THEN 2
WHEN af.ticket_age > 90 THEN 3
END AS range_sort,
CASE WHEN af.ticket_age BETWEEN 0 AND 30 THEN '30 days'
WHEN af.ticket_age BETWEEN 31 AND 60 THEN '30 - 60 days'
WHEN af.ticket_age BETWEEN 61 AND 90 THEN '61 - 90 days'
WHEN af.ticket_age > 90 THEN '> 90 days'
ELSE 'Other'
END AS ticket_age_range

FROM
staging_zendesk.ticket AS t
LEFT OUTER JOIN add_age AS af ON af.id = t.id
)

SELECT
et.range_sort,
et.ticket_age_range,
COUNT(et.ticket_age)
FROM
enhanced_ticket as et
WHERE
/* Optional limiter for date. Can use BETWEEN here as well to set a range. */
et.created_at >= '2021-01-01'
group by 1,2

Something else to consider:  What if a ticket is placed in 'Pending' status.  Is the ticket still considered as 'aging' since the time clock for the SLA is halted?

Zendesk team member

Thanks for sharing the example, David!
In regards to the Pending status, it will be included in the "Unsolved tickets age" since it's essentially capturing the total time elapsed between the ticket creation and resolution. If you're looking for SLA-specific data, we recommend following a similar approach as this recipe (https://support.zendesk.com/hc/en-us/articles/360023884994-Explore-recipe-Reviewing-SLA-performance) to report on the SLA data for your tickets.

Warm Regards,
Eric

Eric G. Gao | Technical Support Architect | Zendesk

Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.