When you start to create your own custom metrics and attributes, you'll use functions a lot to help you perform comparisons, calculations, and manipulations to your business information.
Explore offers many functions to help you work with, for example, dates, text strings, calculations and more. Use this article as a reference to help you discover the available functions and how to use them.
This article contains the following topics:
Getting started
You can browse for, and select functions when you create a standard calculated metric or attribute. In the formula editing window, you can either begin to type the name of a function or click Add under Functions and choose your function from the list.
When the function is added to a formula, it will appear with standard values. You must replace these with the values you want to use in your formula. In this example, you want to return only your tickets with subject lines beginning with "[Flagged]".
- In the calculations menu (
), click Standard calculated attribute.
- Name your calculated attribute. This example uses Subject begins with [Flagged].
- Under Functions, click Add.
- In the formula window, type IF .
- Under Functions, click Add.
- Search for LEFTPART. Either use the search box, click Filter to scope the values down to the Text category, or scroll through the list until you find the function you want.
- Next to LEFTPART, click +.
- You'll now see the formula IF LEFTPART(_text,_number). Change the _text parameter to the Ticket Subject attribute. Either highlight _text and then choose Ticket subject from the Fields menu, or amend the formula manually to add the attribute.
The formula will now read IF LEFTPART([Ticket subject],_number)
- Change the text _number to the number of characters you want to compare. [Flagged] has nine characters, so enter 9.
The formula will now read IF LEFTPART([Ticket subject],9)
- Finally, add the condition we are searching for; in this case, the text "Flagged". The final formula will read:
IF LEFTPART([Ticket subject],9)="Flagged" THEN [TicketID] ENDIF
Now, when you use this attribute in your report, it will only return tickets with a subject line beginning "[Flagged]"
For more help and examples, see Writing Explore formulas.
Basic mathematical operators
In addition to the above functions, you can also use the following basic mathematical operators to perform calculations and connect text:
Operator | Description |
+ | Add two numeric values, or join two text strings. |
- | Subtract one value from another. |
* | Multiply two values |
/ | Divide one value by another |
= | Test if one value is equal to another value |
!= | Test if one value is not equal to another value |
% | Computes the modulo (or remainder) after dividing one value by another.
Example: 5%2 returns 1, 20%3 returns 2 |
Attribute functions
These functions let you perform basic operations to quantify the number of attributes returned by your query.
Function | Description |
COUNT_VALUES(_attribute) | Counts the number of values of the attribute you supply. Repeat values are included.
Example: COUNT_VALUES([Assignees]) returns the total number of names assigned to tickets. |
DCOUNT_VALUES(_attribute) | Counts the distinct number of different values of the attribute you supply.
Example: DCOUNT_VALUES([Assignees]) returns the total number of different names assigned to tickets. |
Date functions
Explore lets you create, compare, and perform calculations on dates and times. These functions are particularly useful because you'll likely spend a lot of time examining your company information over a chosen date range.
Function | Description |
DATE(_text) | Returns a new date for the text you entered.
Example: DATE("7/24/1980") |
DATE(_year,_month,_day,_hour,_minute,_second) | Returns a new date for the text you entered. You must enter at least the year, month and day.
Example: DATE("1995","06","09","13","15","00") |
DATE_EQUAL(_date,_date) | Returns TRUE if the two entered dates are equal.
Example: IF DATE_EQUAL([Ticket Created - Month],[Ticket Solved - Month]) THEN [Ticket ID] ENDIF Returns all tickets that were solved in the same month they were created. The year isn't included in this example so a ticket that was created in June 2018 and solved in June 2020 would be returned by this example. |
DATE_NOT_EQUAL(_date,_date) | Returns TRUE if the two entered dates are not equal.
Example: IF DATE_EQUAL([Ticket Created - Month],[Ticket Solved - Month]) THEN [Ticket ID] ENDIF Returns all tickets that were not solved in the same month they were created. |
DATE_LESS(_date,_date) | Returns TRUE if the first date is earlier than the second date.
Example: IF DATE_LESS([Ticket Assigned - Date],[Ticket Due - Date - Date]) THEN [Ticket ID] ENDIF Returns all tickets that were assigned before their due date. |
DATE_LESS_OR_EQUAL(_date,_date) | Returns TRUE if the first date is earlier or the same as the second date.
Example: IF DATE_LESS_OR_EQUAL([Ticket Assigned - Date],[Ticket Due - Date - Date]) THEN [Ticket ID] ENDIF Returns all tickets that were assigned before or on their due date. |
DATE_GREATER(_date,_date) | Returns TRUE if the first date is later than the second date.
Example: IF DATE_GREATER([Ticket Assigned - Date],[Ticket Due - Date - Date]) THEN [Ticket ID] ENDIF Returns all tickets that were assigned after their due date. |
DATE_GREATER_OR_EQUAL(_date,_date) | Returns TRUE if the first date is later or the same as the second date.
Example: IF DATE_GREATER_OR_EQUAL([Ticket Assigned - Date],[Ticket Due - Date - Date]) THEN [Ticket ID] ENDIF Returns all tickets that were assigned on or after their due date. |
DATE_ADD(_date,_date_part,_increment) |
Returns the given date, updated by the entered increment. The _date_part parameter could be for example, 'year', 'quarter', 'month', 'day', 'hour', etc. The entered increment must be an integer value, but it can be negative. |
DATE_DIFF(_date,_date,_date_part) | Returns the relative or absolute difference between two dates in the format you choose. _date_part can be one of the following:
For relative differences (whole numbers):
For absolute (exact) values:
Examples: DATE_DIFF("2011/10/22","2008/01/06","year") returns 3 (2011-2008) DATE_DIFF("2011/10/22","2008/01/06","nb_of_years") returns 4 (actual count of years) DATE_DIFF("2019/03/06","2017/03/05","nb_of_days") returns 731 DATE_DIFF("2019/03/06","2017/03/05","day") returns 1 |
DATE_FORMAT(_date,_format) | Returns the entered date in the supplied format.
For a list of the formats you can use, see Formatting dates. |
TODAY() | Returns the current date (GMT) |
NOW() | Returns the current (GMT) date and time (in hours, minutes, and seconds) |
CURRENT_YEAR() | Returns the current year (GMT). |
CURRENT_HALFYEAR() | Returns the current semester (GMT) as "H1" or "H2" |
CURRENT_QUARTER() | Returns the current quarter (GMT) as "Q1", "Q2", "Q3", or "Q4" |
CURRENT_MONTH() | Returns the current month (GMT), for example "January", "February". |
CURRENT_MONTH_NUMERIC() | Returns the current month (GMT) as a number, for example "1" for January, "2" for February. |
CURRENT_DAY() | Returns the current day (GMT) as a number. |
CURRENT_WEEKDAY() | Returns the current day of the week (GMT) as text, for example "Monday", "Tuesday". |
CURRENT_WEEKDAY_NUMERIC() | Returns the current day of the week (GMT) as a number, for example "0" for Sunday, "1" for Monday. |
CURRENT_HOUR() | Returns the current hour (GMT) as a number. |
CURRENT_MINUTE() | Returns the current minute (GMT) as a number. |
CURRENT_SECOND() | Returns the current second (GMT) as a number. |
YEAR(_date) | Returns the year of the supplied date. |
MONTH(_date) | Returns the month of the supplied date as text, for example "January", "February" |
MONTH_NUMERIC(_date) | Returns the month of the supplied date as a number, for example "1" for January, "2" for February. |
DAY(_date) | Returns the day of the supplied date. |
WEEKDAY(_date) | Returns the day of the supplied date as a string, for example "Monday", "Tuesday". |
WEEKDAY_NUMERIC(_date) | Returns the day of the supplied date as a number, for example "1" for Sunday, "2" for Monday. |
HOURS(_date) | Returns the hour of the supplied date. |
MINUTES(_date) | Returns the minute of the supplied date. |
SECONDS(_date) | Returns the second of the supplied date. |
WEEK_NUMBER_ISO(_date) | Returns the week number associated with the supplied date. |
WEEK_NUMBER_MONDAY(_date) | Returns the week number associated with the supplied date starting on Monday. |
WEEK_NUMBER_FRIDAY(_date) | Returns the week number associated with the supplied date starting on Friday. |
WEEK_NUMBER_SATURDAY(_date) | Returns the week number associated with the supplied date starting on Saturday. |
WEEK_NUMBER_SUNDAY(_date) | Returns the week number associated with the supplied date starting on Sunday. |
START_OF_QUARTER(_date) | Returns the start of the quarter for the supplied date. |
END_OF_QUARTER(_date) | Returns the end of the quarter for the supplied date. |
START_OF_MONTH(_date) | Returns the start of the month (GMT) for the supplied date. |
END_OF_MONTH(_date) | Returns the end of the month (GMT) for the supplied date. |
START_OF_WEEK_ISO(_date) | Returns the start of the week for the supplied date. |
END_OF_WEEK_ISO(_date) | Returns the end of the week for the supplied date. |
START_OF_WEEK_MONDAY(_date) | Returns the start of the week that matches the supplied date with the week starting on Monday. |
END_OF_WEEK_MONDAY(_date) | Returns the end of the week that matches the supplied date with the week starting on Monday. |
START_OF_WEEK_FRIDAY(_date) | Returns the start of the week that matches the supplied date with the week starting on Friday. |
END_OF_WEEK_FRIDAY(_date) | Returns the end of the week that matches the supplied date with the week starting on Friday. |
START_OF_WEEK_SATURDAY(_date) | Returns the start of the week that matches the supplied date with the week starting on Saturday. |
END_OF_WEEK_SATURDAY(_date) | Returns the end of the week that matches the supplied date with the week starting on Saturday. |
START_OF_WEEK_SUNDAY(_date) | Returns the start of the week that matches the supplied date with the week starting on Sunday. |
END_OF_WEEK_SUNDAY(_date) | Returns the end of the week that matches the supplied date with the week starting on Sunday. |
TO_MONTH_NUMERIC(_text) | Returns the numeric value of the supplied month, for example "01" for January, "02" for February. |
TO_MONTH_TEXT(_text) | Returns the text value of the supplied month, for example "January" for 01, "February" for 02. |
DATE_FROM_TIMESTAMP(_timestamp) | Returns a date from a supplied timestamp in seconds. |
DATE_FROM_MILLI_TIMESTAMP(_timestamp) | Returns a date from a supplied timestamp in milliseconds. |
DATE_TO_TIMESTAMP(_date) | Returns a timestamp from a supplied date. |
DATE_TO_MILLI_TIMESTAMP(_date) | Returns a timestamp in milliseconds from a supplied date. |
SECONDS_TO_TIME(_time) | Converts seconds to HH:MM:SS format. |
FISCAL_YEAR(_date,_start_month) | Returns the fiscal year based on the supplied date and start month. |
FISCAL_QUARTER(_date,_start_month) | Returns the fiscal quarter based on the supplied date and start month. |
FISCAL_WEEK_NUMBER(_date,_start_month) | Returns the fiscal week number based on the supplied date and start month. |
WEEKYEAR(_date) | Returns the year of the week number associated with the supplied date. |
Formatting dates
The DATE_FORMAT function render full timestamps as different types of dates. Each format is represented by a different letter. You enter the associated letter in the format part of the DATE_FORMAT function. Letters must be entered in double quotes.
Note: You can combine the date or time components to display more information about a date. For example if you want to view the month and year of a date you could use "MM-yyyy", or if you want to view the minutes and seconds of a date you could use "mm-ss". If you choose to use multiple date components at once, you must include a dash between the different formats.
The table shows formats you can use. The examples use the timestamp, 2017-11-03T03:18:25.
Date or time component | Letter | Example using 2017-11-03T03:18:25 |
Era | G | AD |
Year | y | 2017 |
Month number | M | 11 |
Week number | w | 44 |
Day number of year | D | 307 |
Day number of month | d | 3 |
Day name of week | E | Fri |
AM or PM | a | AM |
Hour in day (0-23) | H | 3 |
Hour in day (1-24) | k | 3 |
Hour in AM/PM (0-11) | K | 3 |
Hour in AM/PM (1-12) | h | 3 |
Minutes | m | 18 |
Seconds | s | 25 |
Millisecond | S | 0 |
Timezone (UTC) | z | PST |
Time zone (RFC) | Z | -800 |
Logical functions
These functions give you great flexibility in analyzing and presenting your information just how you want.
Function | Description |
IF THEN ELSE Conditional expression | Enables you to evaluate logical values.
Format: IF (condition) THEN (value if true) ELSE (value if false) ENDIF For in-depth information about this function, see Using the IF THEN ELSE function. |
SWITCH Conditional expression | SWITCH provides a more easy to understand alternative to IF THEN ELSE statements that have multiple branches.
SWITCH (_tested_element) { CASE _value1: _return_value CASE _value2: _return_value DEFAULT: _default_return_value } For in-depth information about this function, see Adding multiple conditional expressions with SWITCH. |
AND | Returns TRUE if the two supplied expressions are TRUE.
Example: IF ([Ticket channel]="Email") AND ([Submitter email]="fred@fredco.com") THEN [Ticket ID] ENDIF Returns all tickets that were received via. email from fred@freedco.com. |
OR | Returns TRUE if at least one of the supplied expressions is TRUE.
Example: IF ([Ticket channel]="Email") OR ([Ticket channel]="Twitter") THEN [Ticket ID] ENDIF Returns all tickets that were received through either the email or Twitter channels. |
NOT | Reverses the logical value of the supplied expression.
Example: IF NOT([Ticket channel]="Email") THEN [Ticket ID] ENDIF Returns all tickets that were not received through the email channel. |
TRUE | Returns the logical value "TRUE". |
FALSE | Returns the logical value "FALSE" |
Numeric functions
These functions enable you to perform a wide range of mathematical calculations on the information in your reports.
Function | Description |
ABS(_number) | Returns the absolute value of the supplied number.
Examples: The absolute value of 1 is 1 The absolute value of -7.3 is 7.3 |
BETWEEN(_number,_number,_number) | Returns TRUE if the first supplied number is between the two other numbers.
Example: BETWEEN(SUM(Profit),500,1500) returns TRUE if SUM(Profit) is equal to, or between 500 and 1500. |
CEIL(_number) | Returns the ceiling of the supplied number. The ceiling of a number is its highest closest or equal integer.
Examples: The ceiling of 125.4 is 126 The ceiling of -63.2 is -63 CEILING(SUM(First resolution time (min))) returns the highest closest integer to each first resolution time stored in the attribute. |
EXP(_number) | Returns the value of the base of the natural logarithm (e) to the power of the supplied exponent.
Example: EXP(SUM(Unit price)) returns the natural logarithm of each value of the SUM (Unit price) metric. |
FLOOR(_number) | Returns the floor of the supplied number. The floor of a number is its lowest closest or equal integer.
Examples: The floor of 125.4 is 125 The floor of -63.2 is -64 FLOOR(SUM(First resolution time (min))) returns the lowest closest integer to each first resolution time stored in the attribute. |
GROWTH_RATE(_number,_number) | Returns the growth rate corresponding to the two supplied parameters.
Example: If the two supplied parameters are X and Y, the growth rate is Y-X/ABS(X). |
INTEGER(_param) | Returns the integer of the supplied non-integer number.
Example: INTEGER(1.56) returns 1 |
IS_NAN(_value) | Returns TRUE if the value is not a number (NAN).
Examples: IS_NAN("Copenhagen") returns TRUE IS_NAN(235.78) returns FALSE |
MAXIMUM(_number,_number) | Returns the largest of the two supplied parameters.
MAXIMUM(SUM(Revenues),SUM(Expenses)) returns the larger of Revenues and Expenses. |
MINIMUM(_number,_number) | Returns the smallest of the two supplied parameters.
MINIMUM(SUM(Revenues),SUM(Expenses)) returns the smaller of Revenues and Expenses. |
NUMBER(_param) |
Returns the number representation of the given parameter, or returns NaN when it cannot be displayed as a number. Examples: NUMBER("3.14116") returns 3.14116 NUMBER ([Box category]) returns the number of the current value of the Box category attribute. |
PI() |
Returns the mathematical constant for the ratio of the circumference of a circle to its diameter, expressed as PI. The value is 3.141592653589793. |
POWER(_number,_number) | Computes and returns the first supplied number to the power of the second supplied number.
Example: IF SUM(Unit Price)=2 then POWER(SUM(Unit Price),3) returns 8 |
RANDOM() |
Returns a pseudo-random number 'n' where 0<=n<1. If you need a random integer, use the formula Round(Random()*100). |
ROUND(_number) |
Rounds the value of supplied number up or down to the nearest integer. If equidistant, the value is rounded up. |
SIGN(_number) |
Returns -1 if the number is negative, 0 if zero, and 1 if positive. |
SQRT(_number) | Computes and returns the square root of the supplied number. |
SQUARE(_number) | Computes and returns the square of the supplied number. |
LN(_number) | Returns the natural logarithm of the supplied number. |
LOG10(_number) | Returns the Base-10 logarithm of the supplied number. |
LOG2(_number) | Returns the Base-2 logarithm of the supplied number. |
LOG(_base,_number) | Returns the logarithm of the supplied number to the supplied base. |
Text functions
These functions enable you to perform operations that search and evaluate text. You can also modify text using formulas and patterns you create.
Function | Description |
CONTAINS(_text,_text_to_search) | Returns TRUE if the first entered parameter contains the second one as a substring.
Example: CONTAINS([Submitter name],"Sally")
|
ENDSWITH(_text,_text_to_search) | Returns TRUE if the first supplied text ends with the second supplied text.
Example: ENDSWITH([US State],"ippi") returns TRUE for Mississippi, but FALSE for Delaware. |
FIND(_text,_text_to_find,_number_start_index) |
Returns the index, in the first entered text, of the first instance of the second entered text, from the entered start index. Returns -1 if the second entered text is not found. The index of the first character of a string is 0. Examples: If [Ship Mode]="Urgent" then FIND([ShipMode","Norm",0) returns -1 If [Ship Mode]="Normal" then FIND([ShipMode","ma",0) returns 3 If [Ship Mode]="Urgent" then FIND([ShipMode","ge",2) returns 0 |
LEFTPART(_text,_number) | Returns the leftmost _number of characters in the supplied text.
Example: If [Department]="Technical documentation" then LEFTPART([Department],9) returns "Technical" |
LENGTH(_text) | Returns the length (number of characters) of the supplied text.
Example: LENGTH([Product category]) returns the length of each value of the Product category attribute. |
LOWERCASE(_text) | Converts and returns the supplied text in lower case. |
LTRIM(_text) | Returns the supplied attribute value with any whitespace to the left removed.
Example: LTRIM([Name]) would return "Peter " if [Name] is " Peter " |
REPLACE(_text,_text_to_replace,_text_to_replace_with) | Returns a copy of _text in which all instances of _text_to_replace have been replaced with _text_to_replace_with.
Example: IF [US State]="new York" then REPLACE([US State],"n","N") returns "New York" |
RIGHTPART(_text,_number) | Returns the rightmost _number of characters in the supplied text.
Example: If [Department]="Technical documentation" then RIGHTPART([Department],13) returns "documentation" |
RTRIM(_text) | Returns the supplied attribute value with any whitespace to the right removed.
Example: TRIM([Name]) would return " Peter" if [Name] is " Peter " |
STARTSWITH(_text,_text_to_search) | Returns TRUE if the first supplied text starts with the second supplied text.
Example: STARTSWITH([US State],"Miss") returns TRUE for Mississippi, but FALSE for Delaware. |
IS_POSITIVE(_text) | Returns TRUE for positive and FALSE for negative based on analysis of the text. |
SUBSTR(_text,_number_start_index,_number_end_index) |
Returns part of _text consisting of the character specified by start index (the first entered integer) and all characters up to end index-1 (the second entered integer). Example: SUBSTR("Web marketing",0,3) returns "Web" |
TRIM(_text) | Returns the supplied attribute value with any whitespace to the left or right removed.
Example: TRIM([Name]) would return "Peter" if [Name] is " Peter ": |
UPPERCASE(_text) | Converts and returns the supplied text in upper case. |
STRING(_number) | Converts the supplied number into a string. |
LPAD(_text,_length,_pad) | Pads text to the left of _text with _pad until the string is at _length.
Example: LPAD("1",7,"?") returns "??????1" |
RPAD(_text,_length,_pad) | Pads text to the right of _text with _pad until the string is at _length.
Example: RPAD("1",7,"?") returns "1??????" |
LINK(_url,_label) | Returns a HTML link pointing to the supplied URL.
For an example, see Explore recipe: Configuring clickable links to tickets. |
Tag functions
These functions help you search for the presence or absence of tags. This can include ticket tags, call tags, or anything else that uses tags.
Function | Description |
INCLUDES_ALL([tag attribute], "tag1", "tag2")
or INCLUDES([tag attribute], "tag1", "tag2") |
Returns TRUE if all of the specified tags are included in the tag attribute.
Examples: INCLUDES_ALL([Ticket tags], "London", "Milan") returns TRUE if a ticket contains the tags "London" and "Milan". NOT INCLUDES_ALL ([Ticket tags], "London", "Milan") returns TRUE if a ticket does not contain both of the tags "London" and "Milan". For more examples of how to use this function, see Reporting with tags. |
INCLUDES_ANY([tag attribute], "tag1", "tag2") | Returns TRUE if any of the specified tags are included in the tag attribute.
Examples: INCLUDES_ANY([Ticket tags], "London", "Milan") returns TRUE if a ticket contains the tags "London" or "Milan". NOT INCLUDES_ANY ([Ticket tags], "London", "Milan") returns TRUE if a ticket does not contain either of the tags "London" or "Milan". For more examples of how to use this function, see Reporting with tags. |
Array functions
This function helps you to search arrays for the information you need.
Function | Description |
IN(_param,_array) | Returns TRUE if the first entered parameter is in the array specified in the second parameter.
Examples: IN(200,ARRAY(100,200,300,400)) returns TRUE IN(250,ARRAY(20,40,60,80)) returns FALSE |
Trigonometric functions
These functions help you to create advanced formulas for measuring angles and distances.
Function | Description |
ACOS(_number) | Computes the arc cosine of the entered number, in radians. |
ASIN(_number) | Computes the arc sine of the entered number, in radians. |
ATAN(_number) | Returns the value in radians of the angle with the tangent entered in the parameter. The value returned is between negative PI/2 and positive PI/2. |
ATAN2(_number,_number) | Returns the angle of the point Y/X in radians when measured counterclockwise from a circle's X axis (0,0 represents the center of the circle). The return value is between positive PI and negative PI. Enter the Y coordinate as the first parameter. |
COS(_number) | Computes the cosine of the entered number, in radians. |
DEGREES(_number) | Converts the given number from radians to degrees. |
RADIANS(_number) | Converts the given number from degrees to radians. |
SIN(_number) | Computes the sine of the entered number, in radians. |
TAN(_number) | Returns the tangent of the entered angle. |
Administration functions
The following administration functions appear in the Explore function list, but are not currently used.
- TEAM
URL functions
The following URL functions appear in the Explore function list, but are not currently used.
- HOST
- DOMAIN
- TLD
JSON functions
The following JSON functions appear in the Explore function list, but are not currently used.
- JSON_EXTRACT
- JSON_ARRAY_LENGTH
- JSON_EXTRACT_ARRAY_ELEMENT
REGEXP functions
A regular expression (sometimes known as a regex, or regexp is a sequence of characters that define a search pattern. Explore contains support for using regular expressions to accomplish the following calculations in your formulas:
Function | Description |
REGEXP_MATCH(_text,_regexp) | Returns true if the text matches the regular expression. |
REGEXP_EXTRACT(_text,_regexp) | Returns the portion of text that matches the capturing group in the regular expression. |
REGEXP_REPLACE(_text,_regexp,_replace_text) | Returns a string where any substring of text that matches the regular expression is replaced by replace_text.
Example: REGEXP_REPLACE("Hello","lo","p") returns "Help" |
For more information about regular expressions, see this Wikipedia page.
Cryptography functions
Explore supports the following cryptography functions:
Function | Description |
HMAC(_algorithm,_output_format,_key,_message) | Returns a keyed hash value using the hash-based message authentication (HMAC) method. |
32 Comments
How do I represent the following timestamp 2018-12-10T06:07:23 as 10/12/2018 17:07?
I've been able to use the custom 'Date display format' to format the date but the time segment isn't particularly clear to me. I presume that Zendesk is saving the timestamp in a different timezone? (I'm based in Melbourne Australia)
Hello Johann,
When building a query, the timestamp will be in UTC time. Adding the query to a dashboard with allow the data to be referenced via the timezone of the user currently viewing the dashboard.
"You can calculate the difference between two days, years, quarters, half years, hours, minutes, or seconds using the DATE_DIFF functions"
Does this mean that weeks and months are specifically excluded?
Greer,
At this time these are the only formatting options we have in Explore. Our team is working on releasing new scheduling updates to individual users later this year.
Celia,
I believe Jessica just missed explicitly stating them, but you should be able to use weeks and months as well.
Celia,
My apologies, but it looks as though week is not possible. This is a screenshot from the function definition inside of Explore.
Well, nobody will reply to my support ticket, so I guess I'll try again here. This article still appears to be impossible. "This section lists the available date functions. In the Functions window, you can view only date functions by clicking the Filter button, then typing Date." As far as I can tell, there's no filter button in the functions window, and if you do field instead of filter, you don't have any of the options in this article.
Hi Celia -
I checked on your ticket and have asked that someone get back to you.
Hi Celia, I'm sorry you had difficulties with the article. I agree that the filters could have been explained better and I've made some changes that hopefully help with that. I hope this helps!
Is it possible in Explore to measure the time in hours that a ticket had a specific value applied within a ticket field? For example, let's say we have a drop-down field with Options 1, 2, and 3. Can we build a query that shows us how many hours a ticket had Option 1 applied before an agent updated it to Option 2?
Hello,
Is there a _date_part that would return the number of business days from the configured schedule instead of all calendar days returned by nb_of_days ?
Thanks,
Traian
Hey folks,
Playing around trying to create a query that will eventually show me the break down of time spent in each state and playing with Date_diff I noticed it seems the newest date needs to be first, but the article says the opposite.
How I tested:
Filter: Tickets created last week
custom metric:
DATE_DIFF([Ticket Created - Date],TODAY(),"day")
Metric:
Display MAX of that metric
Columns
Ticket ID
Result
A column graph that shows negative numbers
Flipping to
Columns -> By Ticket ID
DATE_DIFF(TODAY(),[Ticket Created - Date],"day")
shows positive numbers
Hi @Traian! Unfortunately there isn't a way to do this at this time.
@Shaun - great catch! I validated this with the support team and you are correct. I'm going to flag this article for updates. As an aside, I like using "nb_of_days" as "day" here will just return days in the month:
I'm going to suggest that update as well. Cheers!
Hi - I have a custom "Due Date" field. Is there a way to filter by tickets that are past the Due Date set on the ticket? Showing over due tickets?
Thanks,
Brian
Hi Brian!
You can create a View that can filter your tickets using your custom Ticket field (Due Date). You can learn more about it https://support.zendesk.com/hc/en-us/articles/218032348-Understanding-custom-ticket-fields-in-business-rules-and-views#topic_znk_mnk_xj
Hi, I have a problem to create calculation based on my metrics. Looks like i can't divide using basic operators.
I want to divide a value in a cell with the sum of all data, which I use a metrics before to count a value in each cell.
Ex : I have a metrics A with if function inside, and I want to divide with summary of all value from metrics A, but I dont get any result
Thank you
Makmur
It sounds like you are calculating a percentage based on your original metric?
If you want to display both the original metric and the percentage value on your table, you need to make a copy of your metric. Then use Result Configuration>Result Path Calculation and set one of you metrics to show:
to give:
Nice! Thank you Graeme
Hi Graeme, I think I have another problem here.
I want to divide two of my own metrics because I want to get a result from this kind of formula : a/(b/c)
while a is a metric for each assignee name and b is sum of a and c is total of assignee name
I already have 2 metrics, lets call it metric 1 for a and metric 2 for (b/c)
Heres the calculation for metric 1 :
IF ([Comment present]) AND ([Update ticket status - Unsorted]="Solved") THEN [Ticket ID] ENDIF
And for metric 2 :
D_COUNT(metric 1)/c while c is reffering as above
And when I want to divide metric 1 with metric 2 as
VALUE(metric 1)/VALUE(metric 2)
and it shown nothing in result.
Thank you.
Makmur
If you have created custom metrics and need to perform further calculations using these metrics, go to Result Manipulation>Result Metric Calculation and build your new formula there.
Very nice! Thank you so much Graeme!
Hi there,
is there a way to always round up? Like the actual EXCEL function ROUNDUP?
Kind Regards Wolf
Wolf
If you use the ROUND function like this, you should get the same as ROUNDUP
Thank you Graeme, that would have worked too.
I have found the command, it is CEIL(VALUE), like ceiling from SQL.
Kind regards
Wolf
Wolf
Yes that is much better, Thank you for sharing.
Hi,
How do I get previous month filter in metric using Explore formulas? Is there something similar to THIS-1 for Month (Solved) in Insights?
Andrei
There is a pre-built metric that may help you.
In the Support Tickets dataset, look for the Tickets solved-Last month metric. This metric is built by Zendesk, but you can build your own 'Date range calculated metrics'.
Hi Graeme,
Thx for the information! This metric allows me to use OUNT or D_COUNT only, but I need to use SUM for a Numeric field calculation. How do I get this?
Andrei
Go to Calculations>Date Range Calculated Metric to define your metric.
By default all the aggregators will be available.
Our tickets have a multi-select field that we use to track the ticket escalation path through multiple tiers of support. If a ticket is assigned to one of those tiers, that tier's value will be added to the field for that ticket.
We would like to build a report that only shows the *maximum* escalation level for each ticket over time. So, for example, if a ticket has the values of both V1 and V2 for the field, we would like to *only* count the ticket on the chart as V2. Currently, when I build charts using this field we see tickets double-counted, showing as both V1 and V2 (where the metric is Count(Tickets) and the Rows are the field in question).
How would I compose an Explore metric or attribute that only reports a single "maximum" value for a multi-select field for each ticket?
Ben
Try this:
Use the Ticket Updates dataset
Create a new Standard Calculated Metric to convert your dropdown menu to a number. If your dropdown values look like numbers, you can do this:
IF [Changes - Field name] ="Dropdown Number" then
NUMBER([Changes - New value])
ENDIF
In the above my custom dropdown field is named 'Dropdown Number' which would be your escalation custom field.
Now you can use a MAX aggregate to find the highest values:
So I believe the trick is to get your text drop down field as a number so you can manipulate it to find the highest value.
Please sign in to leave a comment.