Common function syntax FAQ

Q1: Can't the fields in the formula be entered manually?

A: No, the field needs to be selected in the field selection box on the left, and cannot be entered manually. For details, see: Function calculation format.

Q2: How does "not equal" in BI mean?

A: !=. For details, please refer to: calculation operators and precedence.

Q3: How to write text expressions in formulas?

A: Need to add "double quotation marks for English input method" around the text expression. For details, see: Function calculation format.

Q4: What is the standard for capitalization in the formula?

A: All punctuation marks such as commas and parentheses need to use the "English input method".

Q5: Why does the formula report an error "Illegal"?

A: Troubleshoot the reason for the illegal formula.

Q6: If an aggregate function is used in one field in a formula, should the aggregate function be used uniformly in other fields?

A: Yes.

Q7: Can formulas be written in the component title?

A: No.

Q8: How to make condition judgment in BI?

A: IF function and OR, AND function nested use/multiple IF functions nested use. For details, see: Multi-conditional assignment. 

Q9: What is the difference between "" and null?

A: The extracted data is not distinguished, and the real-time data depends on each database type. To ensure the accuracy of the formula, it can be written in a format such as: IF (house type=""||house type=null, building nature, house type).

Q10: How to use || and &&?

A: Logical and logical or usage is the same as AND and OR.

For example, the formula: IF(house type=" "||house type=null,building type,apartment type) is the same as IF(OR(house type=" ",apartment type=null),building type,apartment type). Both mean if the apartment type is empty or blank , then return the data corresponding to the "building property" field, otherwise return the data corresponding to the "house type" field.

Q11: If there is a case where the denominator is 0, how do you write a formula if you want to use division for calculation? If the denominator is 0, can it be corrected directly in the BI formula?

A: Divide when the numerator/denominator is 0.

Q12: How to express with formula if the text field is not empty?

A: len(field)!=0

Q13: Why is the formula "Access Time<"2020-12-01"" illegal?

A: One is a time field and the other is a text field. You need to convert the text field into a time field. Use the date function: Access time>DATE(2020,12,1)

Q14: Why is the formula AVG_AGG (Counter) illegal?

A: 'Counter" is actually COUNT_AGG (Counter), which is already an aggregate function. The aggregate function cannot be nested with the AVG_AGG() aggregate function.

Q15: Why is the wording of "Start Date<NOW()<End Date" illegal?

A: Need to use logical AND() or "&&". For example: start date<NOW()&&NOW()<end date.

Q16: How to calculate the time difference between two time points?

A: DATEDIF or DATESUBDATE function.

Q17: Why is it illegal to use the field creation formula display after detail filtering?

A: The indicator field creation formula after detail filtering only supports the use of aggregate functions.

Q18: Why is it illegal to use IF function and aggregate function together in nested formulas?

A: Use the IF function to calculate the aggregate function twice. When the formula contains non-aggregated fields, the formula is illegal. For details, see: Troubleshooting for illegal formulas Section 3.2.