Beast mode adding two columns with conditions

Hello Dojo,

I'm having trouble adding two columns with in a conditional case. The following works fine:

SUM(`paid_total`) + SUM(`booked_total`)


But then when I try to add any sort of condition, it fails: "An issue has occurred during processing. We are unable to complete the request at this time."

case
when `payment_number` >= 1
then SUM(`paid_total`) + SUM(`booked_total`)
else 0
end

I've tried wrapping the whole statement in a SUM as it suggests to do in the docs, but that also doesn't work.

SUM(
case
when `payment_number` >= 1
then SUM(`paid_total`) + SUM(`booked_total`)
else 0
end
)

Any thoughts here? I've exhausted every other help page on this topic I could find in the Dojo, to no avail.

Answers

  • Jessica
    Jessica ⚪️
    CASE WHEN `payment_number` > 0 THEN SUM(SUM(`paid_total`) + SUM(`booked_total`)) ELSE 0 END
    


  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @domo_sensei2

    Domo cannot apply a CASE statement AFTER the aggregate.

    If you need to apply a CASE statement on the aggregate, then you have to pre-aggreagte the data in ETL or a Dataset View.

    (you could then JOIN the preaggregated data back onto the transactions to retain the granular detail.

    https://www.youtube.com/watch?v=Esnu1PSxRjM&t=633s

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!