Need help with CASE statement in beast mode.

jvsering · June 4

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 · June 4

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 · June 4

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 · June 4

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 · June 4

HI David,

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

david_cunningham · June 4

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

jvsering · June 4

here you go @david_cunningham

david_cunningham · June 4

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 · June 5

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

Need help with CASE statement in beast mode.

Best Answers

Answers

Categories