Troubleshooting errors in Explore formulas

In Explore, you can write formulas that power custom metrics and attributes. To ensure formulas work correctly, construct them properly. If your formula isn’t constructed correctly, the formula editor displays an error message in real time as you write or edit. The error message identifies the issue and gives guidance on how to resolve it.

This article explains how to fix a misconfigured formula in your reports. It lists the error messages you might encounter in the formula editor and provides advice on fixing your formula, including examples of incorrect and correct formulas.

Note: Effective August 13, 2024, reports with misconfigured formulas that may have worked previously will stop working, and an error message will be displayed instead. For more information, see the article: Announcing deprecation of invalid calculations in Explore.

This article contains the following topics:

• Fixing misconfigured formulas
• Types of error messages in the formula editor

Related articles:

• Formula writing resources
• Explore functions reference

Fixing misconfigured formulas

If a calculated metric or attribute contains a misconfigured formula, it won’t return results. Identify misconfigured formulas by checking the following places:

• The Reports library: Reports that use a misconfigured formula appear in the Updates required category in the Reports library
• The Dashboards library: Dashboards that use affected reports appear in the Updates required tab in the Dashboards library
• The report builder: A warning message appears at the bottom of a report that uses the formula.
• The formula editor: A warning message appears in the formula editor for the affected formula.

To fix a misconfigured formula, open a report that uses the formula and fix it in the formula editor. Then, republish any dashboards that report is used in.

Note: If a misconfigured formula is nested inside of a non-formula-based calculation (such as a date range metric or a group or set attribute), no error message appears in the non-formula-based calculation. To see the error message in the formula editor, open the original nested calculation.

Tip: You can ask report creators to fix their own formulas. The Created by column in the Reports library shows which user created each report. If you don’t see Created by, add it by clicking the columns icon () and selecting Created by.

To update a misconfigured formula

1. In Explore, click the Reports icon () to open the Reports library
2. Click the Updates required tab

   

3. Click one of the listed reports to open it
4. For each calculated metric or attribute:
   1. Click the metric or attribute and then click the pencil icon
   2. Check the formula to identify any misconfigurations. The misconfigured parts of the formula are underlined. An error message tells you what needs to be corrected.

      

   3. (Optional) Create a copy of the metric or attribute before making any changes. This prevents existing reports from being impacted during the troubleshooting process.
   4. Fix the issues with the formula specified by the error message. See the sections below for additional guidance with specific error messages

      Fixing a calculated metric or attribute’s formula in one report also fixes it in any other report it’s used in

   5. (Optional) If you created a copy of the metric or attribute, update the original.
5. Save the report

To republish affected dashboards

1. Click the Dashboards icon () to open the Dashboards library
2. Click the Updates required tab
3. Hover your mouse over one of the dashboards and click Edit
4. In the dashboard, click the drop-down arrow next to Share and select Publish. The next time users look at the shared dashboard, they'll see the latest version
5. Click Publish

Types of error messages in the formula editor

This section lists the following types of error messages and provides advice on resolving them:

• Most common formula errors
• General syntax error messages
• Data type error messages
• Metric aggregator error messages
• Other error messages

Most common formula errors

The video below walks you through how to fix a few common warnings.

See the sections below for additional guidance on specific error messages.

General syntax error messages

If you see the following error message, it means that some of the syntax in your formula is incorrect:

• "There’s an issue with the formula. Check your calculation syntax and try again."

  

The most common reasons for this error are listed in the table below.

Reason	Incorrect formula	Correct formula
You opened but didn’t close a bracket or parenthesis. Or you closed more than you opened.	IF ([Ticket status] = "Solved")) THEN [Ticket ID] ENDIF	IF ([Ticket status] = "Solved") THEN [Ticket ID] ENDIF
You opened but didn’t close the quotation marks.	[Ticket status] = "Solved	[Ticket status] = "Solved"
You didn’t wrap the attribute in brackets.	Ticket status = "Solved”	[Ticket status] = "Solved”
You didn’t add an aggregator in front of a metric.	First reply time (min)	SUM(First reply time (min))
You’re missing an essential part of the IF statement. In this example, the ENDIF is missing.	IF ([Ticket status] = "Solved") THEN [Ticket ID]	IF ([Ticket status] = "Solved") THEN [Ticket ID] ENDIF

Data type error messages

The tables below show error messages related to data types that you might see in the formula editor, including incorrect and correct versions of a formula.

The sections below cover data type error messages:

• Equal, not equal, and less than or greater than expressions
• Arithmetical operations and text value joining
• Functions

Equal, not equal, and less than or greater than expressions

Error message example	Incorrect formula	Correct formula
Can't use [Project stage] as text and 1 as number. Use the same type.	[Project stage]=1	[Project stage]="1" The same data type should be used on both sides of the equality expression. A value wrapped in quotation marks is considered text, but a number without quotation marks is a number. Because Project stage is a text attribute, the value should have quotation marks.
Can't use [Ticket country] as text and [User country code] as number. Use the same type.	[Ticket country]!=[User country code]	[Ticket country]!=[User country name] The same data type should be used on both sides of the inequality expression. Ticket country is text, so the attribute on the right should be text too.
Can't use [Ticket assigned - Date] as text and 26 as number. Use only numbers or only text.	[Ticket assigned - Date] >= 26	[Ticket assigned - Date] >= "2023-01-26" The same data type should be used on both sides of the greater than or equal to expression. In this case, it’s a date wrapped in quotation marks.
Can't use VALUE(Agent replies) as number and 2 as text. Use only numbers or only text.	VALUE(Agent replies)<”2”	VALUE(Agent replies)<2 The same data type should be used on both sides of the less than expression. A value wrapped in quotation marks is considered text, but a number without quotation marks is a number. Because Agent replies is a metric, the value should be a number, so the quotation marks shouldn’t be used.

Arithmetical operations and text value joining

Error message example	Incorrect formula	Correct formula
Can't use [Ticket group] as text and [Ticket ID] as number. Use only numbers or only text. If necessary, use the STRING function to transform numbers to text, or the NUMBER function to transform text to numbers.	[Ticket group]+[Ticket ID]	[Ticket group]+STRING([Ticket ID]) The goal of this formula is to join text as showcased in Explore recipe: Joining attributes. That’s why the numeric attribute needs to be transformed into text via the STRING function.
Can't use VALUE(Full resolution time (min)) as number and [Additional task time] as text. Use only numbers or only text. If necessary, use the STRING function to transform numbers to text, or the NUMBER function to transform text to numbers.	VALUE(Full resolution time (min))+[Additional task time]	VALUE(Full resolution time (min))+NUMBER([Additional task time]) The goal of this formula is to sum up two values. Full resolution time (min) is a number, but Additional task time is a text field with numeric values listed inside of it. The best approach is to create a new numeric custom field in Support for recoding the additional task time, which won’t require any transformation and will work "out of the box" in Explore. Alternatively, the text attribute can be transformed into a number via the NUMBER function.
Can't use VALUE(First reply time (min)) as number and "60" as text. Use only numbers.	VALUE(First reply time (min))/"60"	VALUE(First reply time (min))/60 Arithmetic operations such as division require numeric values. A value wrapped in quotation marks is considered text, but a number without quotation marks is a number. The quotation marks shouldn’t be used here.
Can't use COUNT(Comments) as number and [Comment public] as text. Use only numbers.	COUNT(Comments)-[Comment public]	COUNT(Comments)-COUNT(Public comments) Arithmetic operations such as subtraction require numeric values. Comment public is a text attribute. You need to use a metric instead.

Functions

Error message example	Incorrect formula	Correct formula
Can't use different types in the THEN statement. 1 is number and "0" is text. Use the same type.	IF [Ticket status]="Open" THEN 1 ELSE "0" ENDIF	IF [Ticket status] = "Open" THEN 1 ELSE 0 ENDIF or IF [Ticket status] = "Open" THEN "1" ELSE "0" ENDIF The values used after the THEN and ELSE keywords should be of the same type. A value wrapped in quotation marks is considered text, but a number without quotation marks is a number. So there are two ways to fix this error, as shown above.
Can't use 1 as number and 2 as number. Use only booleans.	IF 1 OR 2 THEN [Ticket ID] ENDIF	IF VALUE(Agent replies) = 1 OR VALUE(Agent replies) = 2 THEN [Ticket ID] ENDIF Conditions set inside of the IF statement should be TRUE or FALSE. The correct syntax is: IF TRUE OR FALSE THEN [Ticket ID] ENDIF Instead, add some conditions. However, the formula editor doesn’t assess the validity of the conditions themselves.
Can't use number in this function. Use text.	NUMBER([Ticket ID])	NUMBER([Ticket external ID]) The NUMBER function is designed to transform text attributes with numeric values into text. The Ticket ID attribute is a numeric field, which is why it can’t be used with this function. However, text attributes such as Ticket external ID can.
Can't use text, boolean in this function. Use text, text or number.	LINK("https://subdomain.zendesk.com/ agent/organizations/" + STRING([Ticket organization ID]), [Organization check box])	LINK(” https://subdomain.zendesk .com /agent/organizations/” + STRING([Ticket organization ID]), [Ticket organization name]) or LINK(” https://subdomain.zendesk .com /agent/organizations/” + STRING([Ticket organization ID]), STRING([Organization check box])) The LINK function expects text as the second parameter, which is why you can’t use a boolean attribute. There are two options, as shown above. You can use a text attribute, or transform the boolean attribute to text via the STRING function.
Can't use [Ticket created - month] as text here. Use timestamp type.	DATE_LAST([Ticket created - Month])	DATE_LAST([Ticket created - Timestamp]) The DATE_LAST function expects a timestamp attribute.
Can't use different condition types. Use text type.	SWITCH ([Ticket group]) {CASE "Support": "1" CASE 3: "2" }	SWITCH ([Ticket group]) {CASE "Support": "1" CASE 3: "2" } Because Ticket group is a text attribute, conditions of all cases should be of the text type. A value wrapped in quotation marks is considered text, but a number without quotation marks is a number. So the 3 needs to be wrapped in quotation marks.
Can't use different types in the SWITCH statement. "1" is text and 2 is number. Use the same type.	SWITCH ([Ticket group]) {CASE "Support": "1" CASE "IT": 2 }	SWITCH ([Ticket group]) {CASE "Support": "1" CASE "IT": "2" } The results of all cases should be of the same type. A value wrapped in quotation marks is considered text, but a number without quotation marks is a number. So the 2 needs to be wrapped in quotation marks.
You can't use different types in the array. Use the same type.	IN([Leg instance], ARRAY(1, "2", "3"))	IN([Leg instance], ARRAY(1, 2, 3)) Use quotation marks for all values if it’s a text attribute, or don’t use quotation marks for all values if it’s a numeric attribute. Leg instance is a numeric attribute, so the formula should not use quotation marks

Metric aggregator error messages

Error message example	Incorrect formula	Correct formula
Can’t use the SUM(database metric) aggregator. Use VALUE aggregator, ATTRIBUTE_FIX or ATTRIBUTE_ADD function.	IF SUM(Requester wait time (min))>120 THEN "Long wait time" ELSE "Short wait time" ENDIF	IF VALUE(Requester wait time (min))>120 THEN "Long wait time" ELSE "Short wait time" ENDIF This error message appears in calculated attributes, not metrics. In this case, you need to use the VALUE aggregator.
Can't use COUNT(Solved tickets) aggregator. Use VALUE aggregator, or wrap the metrics in ATTRIBUTE_FIX or ATTRIBUTE_ADD function.	IF COUNT(Solved tickets)>1 THEN "Solved" ELSE "Unsolved" ENDIF	IF ([Ticket status - Unsorted] = "Solved" OR [Ticket status - Unsorted] = "Closed") THEN "Solved" ELSE "Unsolved" ENDIF This error message appears in calculated attributes, not metrics. The best way to fix this error is to use the same conditions as the ones used in the Solved tickets metric. So you can update the formula as shown above.
Can't use COUNT(Ticket assignments to Dev) in a calculated attribute. Wrap the metric in ATTRIBUTE_FIX or ATTRIBUTE_ADD function.	IF COUNT(Ticket assignments to Dev)>1 THEN "Solved" ELSE "Unsolved" ENDIF	IF ATTRIBUTE_FIX(COUNT(Ticket assignments to Dev), [Update ticket ID])>1 THEN "Multiple escalations" ELSE "One or no escalations" ENDIF This error message appears in calculated attributes, not metrics. If the original formula is more complex and can’t be reused, try using the ATTRIBUTE_FIX function to specify on which levels this attribute should be aggregated. In this case, it should be calculated on the Update ticket ID level.
Time in status already contains aggregators. Either replace AVG with SUM, or ensure that Time in status contains only VALUE aggregators.	AVG(Time in status)	SUM(Time in status) Time in status is a nested metric. Its formula already contains an aggregator. For example, it could be MED(First reply time (hrs)). Calculating an average on top of a median is not allowed, so the top-level aggregator must be set to SUM or the lower-level aggregator needs to be changed to VALUE.

Other error messages

Error message example	Incorrect formula	Correct formula
[Ticket statuses] doesn't exist in this dataset. Check if there is a typo.	[Ticket statuses] ="Solved"	[Ticket status] ="Solved" The most common reasons for this error are: The attribute or metric used is from another dataset. There’s a typo in the name of the attribute or metric. The attribute or metric name isn’t written in the user’s profile language. The formula references a calculated metric or attribute that includes quotation marks in the name. Remove the quotation marks to resolve the error. In this example, there was a typo in the name of the attribute.
[Calculated group] has a wrongly configured formula. Fix it before saving.	IF [Calculated group] = "light" THEN TRUE ELSE FALSE ENDIF	Calculated group has some misconfiguration in its formula. So you need to fix that first.
Calculation is referencing itself here COUNT(Sales tickets). Remove or replace it.	COUNT(Sales tickets)/COUNT(Tickets)	You’re adding a formula inside of itself. In this case, the formula being edited is Sales tickets, and the user is trying to add Sales tickets inside of the formula.
Max of 3 levels in a calculation. Remove the nested calculation here: SUM((2nd reply time)	MED(2nd reply time) / MED(First reply time (min)	The 2nd reply time calculated metric is based on another calculation that is itself based on another calculation. Such calculations are referred to as nested. Only up to 3 nested levels are allowed.
Can't use [Ticket Group] here. INCLUDES_ALL can only be used on tag attributes.	INCLUDES_ALL([Ticket Group], "Support", "Sales")	INCLUDES_ALL([Ticket Tags], "support", "sales") IN([Ticket Group], ARRAY( "Support", "Sales")) INCLUDES functions are allowed only with the tag attributes. For other attributes, use the IN function.
[Date range calculated metric] has a wrongly configured formula. Fix it before saving.	COUNT([Date range calculated metric])	Date range calculated metrics are not supported in calculated metrics. To perform calculations using date range calculated metrics, utilize a result metric calculation instead.