• Thibaut

  • May 11, 2021 13:46

  Hi Dinara,

  You should be able to see the number of tickets received on N1 and N2 using the date range calculated metrics, however I am not sure I understand what you mean by "the distribution of those tickets by tags".
  Could you give me more details about with maybe an example ?

  I would then be able to look for a potential solution.

  Thibaut | Customer Advocate | EMEA
  👉 Free Zendesk trainings 👈

• Dinara

  • May 11, 2021 15:18

  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!

• Thibaut

  • May 13, 2021 15:21

  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 :)

• Dinara

  • May 13, 2021 16:42

  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? 

• Thibaut

  • May 14, 2021 07:50

  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.

Please sign in to leave a comment.