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 |
> |
Test if the first value is greater than the following value (x>y) |
< |
Test if the first value is less than the following value (x<y) |
>= |
Test if the first value is greater than or equal to the following value (x>=y) |
<= |
Test if the first value is less than or equal to the following value (x<=y) |
% | Computes the modulo (or remainder) after dividing one value by
another. Example: 5%2 returns 1, 20%3 returns 2 |
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. |
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]="Voice") THEN [Ticket ID] ENDIF Returns all tickets that were received through either the email or voice 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" |
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 |
SWITCH Conditional expression | SWITCH provides an easier-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. |
Attribute functions
These functions let you perform basic operations to quantify the number of attributes returned by your report. You can also perform more advanced operations to "lock in" or extend the aggregation level of your calculations.
Function | Description |
COUNT_VALUES(_attribute) | Counts the number of values of the attribute you supply. Repeat values
are included. Can be used as a condition in calculated attributes and
metrics, but cannot be used in the THEN clause of a calculated
metric. Example: IF COUNT_VALUES([Ticket ID])>30 THEN 1 ELSE 0 ENDIF The example above returns 1 when there are more than 30 tickets, and 0 when there are fewer than 30.
Tip: If you want to create a metric
that counts the number of the values of an attribute, the metric
formula should be simply [Attribute]. You can then apply the COUNT aggregator
as normal.
|
DCOUNT_VALUES(_attribute) | Counts the distinct number of different values of the attribute you
supply. Can be used as a condition in calculated attributes and metrics, but
cannot be used in the THEN clause of a calculated metric. Example: IF COUNT_VALUES([Organization name])>100 THEN 1 ELSE 0 ENDIF The example above returns 1 when there are more than 100 distinct organizations, and 0 when there are fewer than 100.
Tip: If you want to create a metric
that counts the number of distinct values of an attribute, the
metric formula should be simply [Attribute]. You can then apply the D_COUNT
aggregator as normal.
|
ATTRIBUTE_FIX(aggregator(metric name), attribute1, attribute2) |
Returns the value of aggregator(metric name) aggregated by the attributes you specify (in this example attribute1 and attribute2). No other attributes can affect the results from this metric. Calculated attributes can’t be used inside of the ATTRIBUTE_FIX function. Example: ATTRIBUTE_FIX(MED(First Reply Time (min), [Ticket created - Year], [Ticket created - Month]) For more help and examples, see Working with aggregation level functions. |
ATTRIBUTE_ADD(aggregator(metric name), attribute1, attribute2) |
Returns the value of aggregator(metric name) aggregated to all attributes in the report in addition to attribute1 and attribute2. Calculated attributes can’t be used inside of the ATTRIBUTE_ADD function. Example: ATTRIBUTE_ADD(MED(First Reply Time (min), [Ticket created - Year], [Ticket created - Month]) For more help and examples, see Working with aggregation level functions. |
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") | 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. |
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.
For examples of how to use some of these text functions (like CONTAINS, LEFTPART, SUBSTR, FIND, and LENGTH) see Explore recipe: Reporting on nested drop-down fields.
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. |
Regex 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. For an example of how to use this function, see Can I report on user email domain in Explore? |
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.
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) | Converts the supplied text into a date with a UTC timestamp. The
entered text can be in a variety of formats, but must at least include the
month, day, and year. Examples:
|
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 in the user's time zone, 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 exact difference between two dates in the
format you choose. In the function, _date_part can be one of the
following: For relative differences (whole numbers):
For 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","2019/03/05","day") returns 1 DATE_DIFF("2019/03/06","2017/03/05","nb_of_days") returns 731 |
DATE_FORMAT(_date,_format) | Returns the entered date in the user's time zone in the supplied
format. For a list of the formats you can use, see Formatting dates. |
TODAY() | Returns the current date in the user's time zone. |
NOW() | Returns the current date and time in the user's time zone (in hours, minutes, and seconds). |
CURRENT_YEAR() | Returns the current year. |
CURRENT_HALFYEAR() | Returns the current semester as "H1" or "H2" |
CURRENT_QUARTER() | Returns the current quarter as "Q1", "Q2", "Q3", or "Q4" |
CURRENT_MONTH() | Returns the current month, for example "January", "February". |
CURRENT_MONTH_NUMERIC() | Returns the current month as a number, for example "1" for January, "2" for February. |
CURRENT_DAY() | Returns the current day as a number. |
CURRENT_WEEKDAY() | Returns the current day of the week as text, for example "Monday", "Tuesday". |
CURRENT_WEEKDAY_NUMERIC() | Returns the current day of the week as a number, for example "0" for Sunday, "1" for Monday. |
CURRENT_HOUR() | Returns the current hour in the user's time zone as a number. |
CURRENT_MINUTE() | Returns the current minute in the user's time zone as a number. |
CURRENT_SECOND() | Returns the current second in the user's time zone 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(_date) | Returns the week number associated with the supplied date. See Understanding how the start of the week affects week numbers. |
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 for the supplied date. |
END_OF_MONTH(_date) | Returns the end of the month for the supplied date. |
START_OF_WEEK(_date) | Returns the start of the week for the supplied date. See Setting the start of the week for reports and filters. |
END_OF_WEEK(_date) | Returns the end of the week for the supplied date. See Setting the start of the week for reports and filters. |
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. |
DATE_FROM_ISO | Returns a timestamp if the parameter is an ISO standard timestamp
string. Assumes that the timestamp has a timezone, but timestamps without
timezone information can be parsed by setting the optional skip-timezone
parameter to
TRUE. Examples: DATE_FROM_ISO("2020-07-24T13:42:00Z") DATE_FROM_ISO("2020-07-24T13:42EST") DATE_FROM_ISO("2020-07-24T13:42+05") DATE_FROM_ISO("2020-07-24T13:42," TRUE) |
DATE_FROM_TIMESTAMP(_timestamp) | Returns a date from a supplied UNIX timestamp in seconds. |
DATE_FROM_MILLI_TIMESTAMP(_timestamp) | Returns a date from a supplied UNIX timestamp in milliseconds. |
DATE_TO_TIMESTAMP(_date) | Returns a UNIX timestamp in the user's time zone from a supplied date. |
DATE_TO_MILLI_TIMESTAMP(_date) | Returns a UNIX 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. The month parameter must be the full name of the month (e.g., "February", not "Feb"). |
FISCAL_QUARTER(_date,_start_month) | Returns the fiscal quarter based on the supplied date and start month. The month parameter must be the full name of the month (e.g., "February", not "Feb"). |
FISCAL_WEEK_NUMBER(_date,_start_month) | Returns the fiscal week number based on the supplied date and start month. The month parameter must be the full name of the month (e.g., "February", not "Feb"). |
WEEKYEAR(_date) | Returns the year of the week number associated with the supplied date. |
DATE_FIRST(time attribute) |
Returns the earliest date or timestamp according to attributes added to the report and is affected by all applied filters. Example: DATE_FIRST([Update - Timestamp]) Returns the earliest update timestamp taking into account all attributes you added to the report. For more help and examples, see Working with earliest and latest date functions. |
DATE_LAST(time attribute) |
Returns the latest date or timestamp according to attributes added to the report and is affected by all applied filters. Example: DATE_LAST([Update - Timestamp]) Returns the latest update timestamp taking into account all attributes you added to the report. For more help and examples, see Working with earliest and latest date functions. |
DATE_FIRST_FIX(time attribute, attribute1, attribute2, ...) |
Returns the earliest date or timestamp according to the attributes specified in the function. Attributes added to the report will not affect the calculation but any filters applied will be taken into account. Example: DATE_FIRST_FIX([Update - Timestamp], [Update ticket ID]) Returns the earliest update timestamp per ticket, regardless of the attributes from the report. For more help and examples, see Working with earliest and latest date functions. |
DATE_LAST_FIX(time attribute, attribute1, attribute2, ...) |
Returns the latest date or timestamp according to the attributes specified in the function. Attributes added to the report will not affect the calculation but any filters applied will be taken into account. Example: DATE_LAST_FIX([Update - Timestamp], [Update ticket ID]) Returns the latest update timestamp per ticket, regardless of the attributes from the report. For more help and examples, see Working with earliest and latest date functions. |
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. You can combine the date or time components to display more information about a date.
For example, to change the default format (2015-01-27T13:21:10) of the Ticket created - Timestamp attribute to a US-style format (Jan 27, 2015 01:21 PM), use this formula:
DATE_FORMAT([Ticket created - Timestamp],"Mon DD, YYYY hh:MI AM")
The table shows formats you can use. The examples use the timestamp 2017-11-03T15:18:25.
Date or time component | Letter | Example using 2017-11-03T15:18:25 |
Year | YYYY | 2017 |
Year | YY | 17 |
Quarter | Q | Q4 |
Month number | MM | 11 |
Month name | Mon | Nov |
Month name | Month | November |
Week of year (1–53) | WW | 44 |
Week of month (1-5) | W | 1 |
Day number of year (01-366) | DDD | 307 |
Day number of month (01-31) | DD | 3 |
Day name of week | Day | Friday |
Day name of week | Dy | Fri |
Hour in day (01-12) | hh | 3 |
Hour in day (1-23) | HH | 15 |
Minutes | MI | 18 |
Seconds | SS | 25 |
Millisecond (000-999) | MS | 0 |
Meridiem indicator (AM or PM) | AM | PM |
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. |
INTEGER(_param) | Returns the integer of the supplied non-integer
number. Example: INTEGER(1.56) returns 1 |
IS_NUMBER(_param) | Returns TRUE if the given parameter is a number (integer or decimal)
and FALSE if it isn’t. Examples:
|
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. |
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. |
53 comments
Erin O'Callaghan
Hi CJ Johnson, you can find more about VALUE and D_COUNT in the article Choosing metric aggregators. Hope that helps!
-1
Dekbi
You may refer to the article Choosing metric aggregators to know more about "VALUE' and "D_COUNT".
You may also use that article to learn about the different aggregators you can use, and how to use them.
Also, for customer metrics, you can change the default aggregator by referring to Setting a metric's default and visible aggregators.
I hope this helps.
0
Crawford Philleo
Should START_OF_WEEK_ISO (and END_OF_WEEK) be returning the start/end of the week based on your account settings? I think this is pulling back Sundays and Saturdays respectively for our account, but I assumed it would be updated based on the specific to the day you select in your account settings per your recent feature upgrade: https://support.zendesk.com/hc/en-us/articles/4418307079322#h_01FTH5XEE308FMZD440QX51AZ1
0
Shehryar Khaliq Lodhi
Is there a way we can achieve the 'subject of triggers fired on chats' like we can escalate that which trigger was used in the most in the chats? the customer interacted with is there a way to achieve?
0
Dekbi
At this time, unfortunately, we don't support reporting for Chat Triggers. What I recommend is to add a custom to every trigger and report on them.
For your reference: Reporting with tags
I've taken a look and found that other users are discussing similar needs here: Reporting on chat triggers
You can up-vote that original post and add your detailed use-case to the conversation. Threads with a high level of engagement ultimately get flagged for product managers to review when they go through roadmap planning.
Specific examples, details about impact, and how you currently handle things are the most helpful things to share to help our product teams understand the full scope of the need when working on solutions.
We truly value customer feedback and your voice and votes on the product feedback topics in the community help influence future Zendesk functionality.
-1
CJ Johnson
Does CONTAINS support multiple arguments? I'm looking for the equivalent to INCLUDES_ALL, but for possible field values for a numeric custom field. I want to say "if the value is one of these IDs, then return TRUE". I'm just not sure if I've hit a bug or CONTAINS can only support a single string to search for.
EDIT: ARRAY is what I really wanted. I found an example in another article, that shows how to use it a little more clearly and it does the trick:
1
Bobby Koch
Is there a way to build a metric that looks for public replies on assigned tickets, if the public reply was within the last 3 days?
0
Dave Dyson
-1
Holli Kyle
Hi there! I've read through the comments here and got a bit further however for some reason my fixed metric is returning a different result from when I filter the data. For the date range, I even tried using DATE_GREATER_OR_EQUAL(Ticket created - Date],"2022/01/01") AND DATE_LESS_OR_EQUAL([Ticket created - Date],"2022/12/31") and got the same exact result however with this format I was at least able to change the dates to see them update to make sure it was working. I'm just not sure why the filter would result in such a hugely different number than the fixed formula?
0
Dane
I will have to directly check your query to determine what could be causing this data discrepancy.
I have created a ticket for you, please wait for my update via email.
-1
Amos Chen
I'm trying to return results for all solved/closed tickets excluding certain tickets containing certain attributes. this is my formula:
IF ([Ticket status - Unsorted] = "Solved" OR [Ticket status - Unsorted] = "Closed" AND [Product Feedback (multi)] != "Null" AND [Product Feedback (multi)]!="(No response needed)") THEN [Ticket ID]
ENDIF
But the results are still returning both of those values. Any ideas what I'm doing wrong?
0
Jacob the Moderator
Hi Amos Chen
I think the NULL shouldn't be in quotes and possibly there is something in the logic of your statement that isn't as intended, I assume you want the product feedback exceptions to apply to both solved and closed tickets, but I could be misunderstanding you.
See if the below gets you closer to your goal.
Hope that helps you out.
0
Jacqui T
The DATE functions no longer work as described in this documentation.
For example - the documentation gives the example of using the DATE() function as 'DATE("1995","06","09","13","15","00")' however this now gives an error. It does work if the date parts are provided as number, but as there is no function supplied to convert numbers stored as strings into numbers stored as numbers we can't make use of this.
This behaviour is one of multiple recent changes to how the date functions work and has broken reports for us. It is not described on the previously published article warning of upcoming changes to Explore.
While the example I gave is picked up by the internal validator, none of the other problems now affecting date functions I've encountered have been - validator says it's find, but the report says it's broken with no advice on what the issue is.
0
Eugene Orman
Jacqui, thank you for pointing this out.
The DATE() function didn't work with the attributes only with values before. We made a change that allows it to work both with the values and attributes. Its purpose is to convert text values into a date with a timestamp.
We haven't yet updated the function description yet, but we will do it both in the UI and this article. The new description will be: Converts the supplied text into a date with a timestamp.
The issues you mentioned are probably not related to this update but to the fact that we stopped executing reports with incorrectly configured formulas as mentioned in this announcement.
In most of the cases, the formula checker lets you know when there is a problem with the calculations but it can not predict all the cases. If you can not find a way to make your reports work please reach out to our support team.
0
Jacqui T
Eugene Orman
The DATE() function no longer works on text values. In fact the formula validator specifically says it requires numbers as input (though will accept the year as text), see screenshot below (using the example from this document).
While it allows me to pass a text formatted timestamp, I cannot then use the value given as a date in other date functions, such as date_format(), whereas previously it formatted these as expected.
0
Eugene Orman
@Jacqui, I have created a proactive ticket so we can have a look at your specific use case.
0
Chetan Bhapkar
HI Team,
What I want to do is
I want to see Ticket Age for each status,
Eg: 1234 is the ticket number it was assigned to xyz and he kept it open I want age, if he change status from Open to pending i want to see what was the between open to pending. Similarly with other ticket's status
0
Gabriel Manlapig
Hi Chetan,
Someone from our team is already assisting you with your request on how to generate a report based on ticket age for each status. Reference ticket #12613567. We would suggest continuing and replying to the ticket if you have further clarifications and questions. Thank you!
0
Jiri Vrla
Hello, I am pretty new in this.
I am trying to build very simple report. I need new metric “Median Full resolution time” but I do not want to see it in minutes but in the format “hh:mm” How can I do that?
MED(Full resolution time (min))
I found only SECONDS_TO_TIME(_time) which should convert second to that but I need to covert minutes. On top of that I am not even sure how to write those formulas
0
Dainne Kiara Lucena-Laxamana
Hi Jiri Vrla


You can try & create a custom calculated metric that mimics the Full Time Resolution metric which converts minutes to seconds. Once it's converted into seconds, you can then edit the display format to Duration. “Duration” convert your results into the hh:mm:ss format (hours, minutes, seconds). This format can be used only for metrics recorded in seconds. This is why you need create a custom metric in the form of seconds.
0
Jiri Vrla
Thank you, Dainne Lucena. Is there a plan to have this as a native function? This is a really bad customer experience. I would expect to be able to choose what time/date format I need.
0
Roosa
How can I create a query that would measure the amount (average) of characters per 1st comment in a submitted ticket? Anything I try comes back with en error.
0
Toru Okada
Hi Team,
I am looking for a solution to concatenate multiple answers.
When I use Multi-select field and customer chooses more than 2 answers, it will show 2 lines in an Explore report.
I would like to show as 1 line = 1 ticket in the report.
Is that possible to make it with "Standard Calculated metric" or any other solutions?
Thank you for your support.
0