NSC | Saved Search Formula > Resolve "Error: Invalid Expression" > CASE WHEN with SUM formula

Scenario

User has a formula that with a CASE expression. The requirement is to get the total of the quotient of 2 fields.

Sample Formula: CASE WHEN {field1} = 'Value' THEN SUM({field2})/SUM({field3}) ELSE 0 END

This formula returns Error: Invalid Expression.

Solution

This is expected behavior since we cannot combine aggregate and non-aggregate SQL functions in the same formula definition. For example, if you create a formula using an aggregate function, then all the functions in the definition must either be aggregate functions or listed in a GROUP_BY clause. This is documented in SuiteAnswers 10101.

Here's the revised formula that would work: