Creating joined attributes
The formula's logic is very simple. You add one attribute to another and make sure that there is a clear visual separator in between, like the space or dash:
[Attribute A]+"-"+[Attribute B]
If you need help creating the attribute, seeCreating standard calculated metrics and attributes.
创建一个属性相结合
- Open a new report using the required dataset.
- In the calculations menu (
), clickStandard calculated attribute. - On theStandard calculated attributepage, give your attribute a name likeTicket created - Year/month.
- In the formula field, enter or paste the following formula:
[Ticket created - Year]+" "+[Ticket created - Month]
- ClickSave.
[Ticket created - Year]+" "+ DATE_FORMAT([Ticket created - Date],"MM")
Joined attribute examples
Day of week/hour
If you need to filter a report by the business hours you can combine the day of the week and hour time dimensions in one attribute and then filter the report via it. To joinTicket created - Day of weekandTicket created - Hourattributes use this formula:
[Ticket created - Day of week]+" "+STRING([Ticket created - Hour])
Year/month
Joining the year and month time dimensions can be useful for sorting or enhancing the report visually. To joinTicket solved - YearandTicket solved - Monthattributes use this formula:
字符串([票年解决])+" "+[Ticket solved - Month]
Ticket group/assignee
Joined ticket group and assignee attribute can be used as the dashboard filter for the small teams to speed-up the value selection process:
[Ticket group]+"-"+[Assignee name]
Two custom fields
Let's say you have two custom drop-down fields Category and Subcategory and you need to look at the specific combinations of these attributes then use this formula:
[Category]+"-"+[Subcategory]
Multiple custom fields with conditions
If you are using the conditional fields feature in Zendesk Support, you might have one category field and multiple subcategory fields that are displayed in the ticket only when a particular category is selected. Placing all the subcategory fields in one report creates a big unusable chart. Instead, use the ExploreIF THEN ELSEfunction to define conditions base on which the subcategories should be joined with the categories. Use this formula and adjust it according to your needs:
IF [Category]="Category A" THEN [Category]+"-"+[Subcategory A]
ELIF [Category]="Category B" THEN [Category]+"-"+[Subcategory B]
ELIF [Category]="Category C" THEN [Category]+"-"+[Subcategory C]
ELIF [Category]="Category D" THEN [Category]+"-"+[Subcategory D]
ELIF ([Category]="Category E" OR [Category]="Category F") THEN [Category]
ENDIF
Using text strings
In this final example, you'll include text to construct a friendly text string for one of your table rows to provide a more detailed status for each ticket.
Use the following formula to accomplish this:
"Status is"+ " " +[Ticket status]+
". Ticket was created in "+ " " +STRING([Ticket created - Year])+ "."
9 Comments
Oh that's awesome@..., that works a treat. Thanks so much!
I have used this one to create an attribute:
In my Report (X-Axis) it's shown as, e.g. "2020 September".
I would like it to appear as, e.g. "2020-09" or "2020-Sep"
How can I do that?
Hey Florian,
For the format 2020-09, you could use the following formula : [Ticket solved - Year]+"-"+ DATE_FORMAT([Ticket solved - Date],"MM")
For the format 2020-Sep, you could use this one: [Ticket solved - Year]+"-"+ LEFTPART([Ticket solved - Month],3)
What could be tricky with this second example is that the years will be correctly ordered (chronologically), but not the months (it will follow an alphabetical order).
So if you wish to use this format 2020-Sep, I recommend to create an Ordered Set attribute based on this joined attribute.
This way you’ll be able to manually order the values so they are in a Chronological order. See this article:Organizing values by groups and sets
Hope this helps!
Hi there,
We have a Custom Field (Plan Type) that has 4 options, and I want to combine two of them ("Null" and "No Account"). How would I go about doing that? Thanks!
Abby
Hi Abby,
You can organize the values thru a custom group attribute (see创建组) based on your custom field attribute. You may aggregate results from these two field options; when you use this custom group attribute in your query, the count for 'Null' and 'No Account' will be combined into one attribute value.
Hi Everyone,
I would create a custom attribute where I would join 2 different form's attributes .
We have 2 form one has got 6 priority level the other has got the standard 3 level . How could I connect them to use only the 3 level of priority instead of different values ?
Thank you in advance,
Patrik
You can try creating a custom attribute with IF THEN ELSE statements referencing your two priority fields. Example:
You can refer to this article to read more about theIF THEN ELSE function.
Thanks Patriarch!
冥界lo, how can I escape double quotes in text?
for example I'm trying to create hyperlink for google sheets and google sheets accepts
=hyperlink("url1","linkname")
format. when I export the report as excel file and import to Google sheets, the hyperlink should work fine.. how can I concat better in explore?
see an example below:
"=HYPERLINK(?https://invicti.zendesk.com/agent/tickets/"+[TicketID]+"/events?comment="+[Update ID]+"?,?"+[Update ID]+"?)"
I need double quotes escaping in replacement of the question marks above. Is this possible?
I'm sorry, we are not able to advise on how to concat your formula better to escape the double quotes to adapt the hyperlink for Google sheets. What you are trying to achieve is tricky because the hyperlink function works differently in Explore compare to other platforms.
For example, in Explore we use the function LINK instead of HYPERLINK :
We do not have an operator or command to escape the double quotes.
Best,
Pleasesign into leave a comment.