Troubleshooting of Invalid Formulas

Scenario

Sometimes, you may enter invalid formulas. This document provides you with some methods of troubleshooting invalid formulas.

You can view the prompt below the formula editing area to determine whether the formulas entered are valid, as shown in the following figure.

Cause

The two main causes are as follows:

1. You may enter incorrect punctuation marks or use the wrong field formats.

2. You may enter formulas that are not consistent with the function logic of FineBI.

Generally, the error message "Syntax error due to marker missing" is displayed when the operators in formulas are not complete.

For example, if you enter ABS(Contract Amount)+, the error message "Syntax error due to marker missing" is displayed.

For example, you need to add a column and assign all values in the column as 1. When you enter "1, the error message "Syntax error due to marker missing" is displayed due to the missing of the closing double quote.

Aggregation functions can only be used for calculation fields. For details about the functions of aggregation functions, see Aggregation Function Overview.

Aggregation functions cannot be used in detail tables. The following figure shows the error message.

Problem: The error message "The aggregation functions cannot be output directly." is displayed when you use aggregation functions.

Cause: The results returned by aggregation functions are aggregated values. For self-service datasets, you can output only fields at the detail level by adding formula columns.

Solution:

1. During component creation, you can use the Add Calculation Field function and enter the corresponding functions for calculation.

2. You can nest aggregations inside the DEF function and output fields at the detail level. For details, see DEF (Specified Dimension).

You cannot use aggregation functions inside aggregation functions. Otherwise, the formulas will be invalid, as shown in the following figure.

For example, you can use the IF function to perform the secondary calculation on the aggregation function. When containing a non-aggregation parameter, the formula is invalid, as shown in the following figure.

In this case, you can change the non-aggregation parameter to an aggregation parameter, as shown in the following figure.

However, aggregation functions can be nested with non-aggregation functions. SUM_AGG(MAX(Field)) is a valid formula, as shown in the following figure.

When parameters set with indicator conditions are used in the formulas for outputting calculation fields, only aggregation functions can be used in the formulas.

For example, you can set indicator conditions for the Contract Amount indicator and add a calculation field, as shown in the following figure.

Since the IF function, which is not an aggregation function, is used, the error message is displayed, indicating that the formula is invalid.

You can replace the indicator conditions with other filter methods.