Beast mode adding two columns with conditions

domo_sensei2
domo_sensei2 ⚪️
in Card Building

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

Categories