Need help with CASE statement in beast mode.

jvsering

I'm getting this error on a pivot table card. The grand totals are correct but I need to see them by data source (Row)

Here's the beast mode I'm using:

    CASE 
        WHEN `Product` != 'SEM' THEN 
            SUM(IFNULL(`Impressions`, 0) / 1000 * IFNULL(`Monday.CPM`, 0))
        ELSE 
            SUM(IFNULL(`Cost`, 0) + IFNULL(`Cost` * `SEM Fee`, 0))
    END

I've also tried the below but running into a syntax error:

SUM(CASEWHEN Product != 'SEM'

THEN IFNULL(Impressions, 0) / 1000 * IFNULL(Monday.CPM, 0)
ELSE IFNULL(Cost, 0) + IFNULL(Cost * SEM Fee, 0)
END

What am I doing wrong?

Thanks!

david_cunningham

you’re missing a closing parenthesis around your sum. You need the sum because you are aggregating your other field.

Try

SUM(CASE WHEN Product != 'SEM'

THEN (IFNULL(Impressions, 0) / 1000) * IFNULL(Monday.CPM, 0)
ELSE (IFNULL(Cost, 0) + IFNULL(Cost * SEM Fee, 0))
END)

david_cunningham

it’s because your columns aren’t wrapped in backticks. You’ll need to add those in.

The code blocks in the forum for some reason tend to remove the backticks.

Wrap your column names in `

david_cunningham

you’re missing a closing parenthesis around your sum. You need the sum because you are aggregating your other field.

Try

SUM(CASE WHEN Product != 'SEM'

THEN (IFNULL(Impressions, 0) / 1000) * IFNULL(Monday.CPM, 0)
ELSE (IFNULL(Cost, 0) + IFNULL(Cost * SEM Fee, 0))
END)

jvsering

HI David,

First, thanks. That' s giving me a syntax error. What else a I missing?

david_cunningham

@jvsering - please send me a screenshot of your exact beast mode.

jvsering

here you go @david_cunningham

david_cunningham

it’s because your columns aren’t wrapped in backticks. You’ll need to add those in.

The code blocks in the forum for some reason tend to remove the backticks.

Wrap your column names in `

jvsering

Thanks David — I was not paying attention! This worked!