Syntax Error - Summary Level Formula

Question

Baby SF user here, I cannot get past this Syntax error saying I am missing a ')' in this formula:

(
    (
        SUM(
            CASE 
                WHEN BucketField_77200030 = 'Promoter' THEN 1, 
                ELSE 0 
            END
        ) - SUM(
            CASE 
                WHEN BucketField_77200030 = 'Detractor' THEN 1, 
                ELSE 0 END
        )
    ) / GROUP_ROWS
) * 100

Can you help?

Answer 1

The parenthesis seem fine, but the SUM and CASE parts of your formula seem to be wrong.

Generally speaking, report formulas use a subset of the functions available for formula fields on SObjects. The complete listing of formula functions is here..

While there isn't a SUM() function that's documented, it does appear to work and be valid. It can take any number of parameters separated by commas (e.g. SUM(1, 2, 3, 4). I wouldn't recommend using it.

The CASE() functions need to be structured differently. The field to check for goes first, then the target-value pairs, and finally the default value (all separated by commas). This one is actually a pretty easy translation from what you have.

CASE(
    BucketField_77200030,
    'Promoter', 1,
    0
)

and you'd do a similar thing for your 'detractor' CASE()

That said, this looks like an attempt at an NPS (Net Promoter Score) calculation. Even with fixing the issues with the formula, I don't think what you have is going to work.

I'm not super familiar with report formulas, so I'm afraid I can't really offer any solid suggestions. An educated guess would be that you would want to:

• Define a formula field on your SObject (and not as a summary formula in your report) to turn promoters/detractors into 1 and -1, respectively
• Simply sum that formula field in your report

So your summary formula would look like My_SObject_Formula_Field__c:SUM / RowCount. If you change your summary formula format to "Percent", then there should be no need to multiply by 100.