Number of tickets received during a specific period of time + division of those tickets into groups by tags

Hi Thibaut!

I tried to photoshop a table for illustrative purposes:

The query should give me the following info:

1. A tag name (one row = one tag);

2. The total number of tickets received within the chosen period;

3. How many tickets with a tag were received within the chosen period;

Say, we received 1000 tickets in total on N1, 41 of those tickets have the "blue" tag, 38 tickets were tagged with "red", and so on.

Creating a data range calculated metrics helps to set the N1 and N2 ranges but I can't make the query count and show both the total number of tickets we received and the number of tickets by each tag in the same query. If I put a "ticket tags" attribute, it only counts how many tickets marked with a tag we received, in all columns. 

Hope I managed to explain it. Thank you for your help!

Hi Dinara, 

In order to create this query you will first need to create a standard calculated attribute like this one: 

With this formula: 

IF INCLUDES_ALL([Ticket tags], "classical") THEN "Classical"
ELIF INCLUDES_ALL([Ticket tags], "electric") THEN "Electric"
ELIF INCLUDES_ALL([Ticket tags], "bass") THEN "Bass"
ENDIF

You will need to change the tags I used for the example with your own tags. 

Then you will need to create two date range calculated metrics like this one : 

Original metric: Tickets
Defined on: Ticket created

Then select the date range for N1, and reproduce the same for N2. 

Finally you will need to create two advanced fixed metrics like this: 

With this formula :

IF ([Ticket created - Date]>="2021-04-04"
AND [Ticket created - Date]<="2021-05-18")
THEN [Ticket ID]
ENDIF

You will need to change the dates to reflect N1 and on the second one, N2. 

Then you can finally place those new metrics and attribute in your query to have something like this: 

You can find here the related articles: 
- Getting started with custom metrics and attributes
- Adding time and date calculated metrics
- Adding fixed calculated metrics

Have a great day :)

I tried creating those metrics but it never shows the total numbers and gives me an error about the SQL query exceeding 50000 rows. :( Is there any way to fix this? 

Hello,

This error would be due to Explore's limit being reached as explained here: Why do I receive a “Network error” in my query?

I would suggest you to reduce the date range of N1 and N2 in order to not go beyond the 50 000 rows, the limit can not be changed or modified so reducing the date range would be the best option.