Need help with CASE statement in beast mode.

Options

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!

Best Answers

  • david_cunningham
    Answer ✓
    Options

    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

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • david_cunningham
    Answer ✓
    Options

    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

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Answers

  • david_cunningham
    Answer ✓
    Options

    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

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • jvsering
    jvsering Member
    Options

    HI David,

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

  • david_cunningham
    Options

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

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • jvsering
    jvsering Member
    edited June 4
    Options

    here you go @david_cunningham

  • david_cunningham
    Answer ✓
    Options

    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

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • jvsering
    jvsering Member
    Options

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