Receiving an error in my Beastmode | Multiple case statements

user046467 · December 2020

I am pretty sure it has something to do with having multiple case statements.

I've the following code which gives me an syntax error:

sum(CASE when `Date` >= (DATE_ADD(CURRENT_DATE(),interval -15 day)) and `Date` <= (DATE_ADD(CURRENT_DATE(),interval -1 day)) then
(case when `ConversionTypeName` like '%Sale%' then `Conversionsvalue`) else 0 end)

But when I use this it workings just fine:

sum(CASE when `Date` >= (DATE_ADD(CURRENT_DATE(),interval -15 day)) and `Date` <= (DATE_ADD(CURRENT_DATE(),interval -1 day)) then
(`ConversionValue`) else 0 end)

How can I fix this?

Thanks in advance

GrantSmith · December 2020

So since you have multiple conditions based on the same date range you'll want to utilize a nested CASE statement like you were originally doing. This will help simplify your beast mode.

Case statements follow the following format:

CASE WHEN (conditions) THEN

Do something

WHEN (another condition) THEN

Do something different

ELSE

Do this if all other conditions failed.

It will execute the first successful condition then exit and won't continue to go through the list of conditions.

You example your format is incorrect since you've got

THEN do something OR

You'd need to do another WHEN statement since you're stating "Hey if the first condition isn't true then do something different for a different condition"

It all boils down to:

sum(
  CASE when `Date` >= (DATE_ADD(CURRENT_DATE(),interval -15 day)) and `Date` <= (DATE_ADD(CURRENT_DATE(),interval -1 day)) THEN
    CASE WHEN `ConversionTypeName` like '%Product1%' THEN
       `Conversions` * 20
    WHEN `ConversionTypeName` like '%Product2%' THEN
       `Conversions` * 14
    ELSE
        0
    END
  ELSE
    0
  END
)

This is the same logic as (just easier to read):

sum(
    CASE WHEN when `Date` >= (DATE_ADD(CURRENT_DATE(),interval -15 day)) and `Date` <= (DATE_ADD(CURRENT_DATE(),interval -1 day)) AND `ConversionTypeName` like '%Product1%' THEN
       `Conversions` * 20
    WHEN when `Date` >= (DATE_ADD(CURRENT_DATE(),interval -15 day)) and `Date` <= (DATE_ADD(CURRENT_DATE(),interval -1 day)) AND `ConversionTypeName` like '%Product2%' THEN
       `Conversions` * 14
    ELSE
        0
    END
)

GrantSmith · December 2020

Hi @user046467

Without seeing the actual error I'm somewhat guessing here but it appears you're missing your final END for your outer case statement. Depending on how you want your SUM to function currently it'll return NULL if your date is not between 1 and 15 days ago. If you want to count those as 0 then you'd need to change your case statement to something like:

sum(CASE when `Date` >= (DATE_ADD(CURRENT_DATE(),interval -15 day)) and `Date` <= (DATE_ADD(CURRENT_DATE(),interval -1 day)) AND `ConversionTypeName` like '%Sale%' then `Conversionsvalue` else 0 end)

user046467 · December 2020

Excellent, thanks a bunch, that worked!

I am always getting a generic error like: Invalid Formula : This calculation contained a syntax error.

Sorry to bother again but I tried to extend the formula a little.
See screenshot.

I've multiple scenario's that's why I inserted OR , which gives me the error above.

If I change it to "when" I don't get an error but the script doesn't work either.

If I remove the seconde line containing "product2" it works fine aswell.

Hopefully you can point me on the right direction again ?

GrantSmith · December 2020

So since you have multiple conditions based on the same date range you'll want to utilize a nested CASE statement like you were originally doing. This will help simplify your beast mode.

Case statements follow the following format:

CASE WHEN (conditions) THEN

Do something

WHEN (another condition) THEN

Do something different

ELSE

Do this if all other conditions failed.

It will execute the first successful condition then exit and won't continue to go through the list of conditions.

You example your format is incorrect since you've got

THEN do something OR

You'd need to do another WHEN statement since you're stating "Hey if the first condition isn't true then do something different for a different condition"

It all boils down to:

sum(
  CASE when `Date` >= (DATE_ADD(CURRENT_DATE(),interval -15 day)) and `Date` <= (DATE_ADD(CURRENT_DATE(),interval -1 day)) THEN
    CASE WHEN `ConversionTypeName` like '%Product1%' THEN
       `Conversions` * 20
    WHEN `ConversionTypeName` like '%Product2%' THEN
       `Conversions` * 14
    ELSE
        0
    END
  ELSE
    0
  END
)

This is the same logic as (just easier to read):

sum(
    CASE WHEN when `Date` >= (DATE_ADD(CURRENT_DATE(),interval -15 day)) and `Date` <= (DATE_ADD(CURRENT_DATE(),interval -1 day)) AND `ConversionTypeName` like '%Product1%' THEN
       `Conversions` * 20
    WHEN when `Date` >= (DATE_ADD(CURRENT_DATE(),interval -15 day)) and `Date` <= (DATE_ADD(CURRENT_DATE(),interval -1 day)) AND `ConversionTypeName` like '%Product2%' THEN
       `Conversions` * 14
    ELSE
        0
    END
)

user046467 · December 2020

Thanks for the superfast response and solution!

I most likely tried that scenario but made a mistake with the " else 0 end"

How should I read these?

I find it hard to know when to use the else 0 end statements. If there is any good documentation (or your explanation) please let me know.

GrantSmith · December 2020

Any time you have an aggregate function (in your case SUM) that is aggregating a record with a NULL value the entire result ends up being NULL. In this case the ELSE 0 is essentially saying instead of returning NULL (by default if there is no ELSE clause) explicitly return 0 so that the SUM will calculate the correct number. In other words the else 0 is stating "add nothing to the total in this case"

Case statements are a standard SQL syntax and there's several websites out there that go over it.

https://www.w3schools.com/sql/sql_case.asp

https://www.sqltutorial.org/sql-case/

jaeW_at_Onyx · December 2020

@GrantSmith sorry, your code may be good, but your reason is innacurate.

you absolutely CAN aggregate across ROWS with the SUM column and include NULLS.

so

SELECT

SUM(

5

4

null

6

) as myTotal

FROM table

will return the expected value of 15.

you CANNOT aggregate across columns (on one row) and include nulls.

SELECT

5 6 4 + null + 6 as myNewColumn

FROM TABLE

will return a NULL.

In other words, the ELSE 0 in this context is not necessary, and potentially not desireable. I assume the reason why the use is applying a date check in the CASE statement is b/c they only want to show a number if the criteria are met. by using ELSE 0, you for data onto rows that you otherwise wouldn't see in Analyzer.

If I had to guess, the conversion factor is a product of the Product, and the date filter is a user decision about what they want to see. So I would actually refactor this as...

sum(
    CASE 
    WHEN when `ConversionTypeName` like '%Product1%' THEN `Conversions` * 20
    WHEN when `ConversionTypeName` like '%Product2%' THEN `Conversions` * 14
    END
)

THEN use date filters in the to limit my data to just the Previous N days. -- this makes your beast mode more reuseable in other contexts,

If you absolutely want the test for DATE, keep it in as Grant had it, but just drop the ELSE 0 and it should still work.

jaeW_at_Onyx · December 2020

in the original code you forgot an END.

when formattting your code, take the time to spread it across lines. that way you can ask yourself, "how should this codeblock begin, how should it end?

sum(

CASE
WHEN `Date` >= ( DATE_ADD(CURRENT_DATE(),interval -15 day) ) -- these parenthesis are superflous

AND `Date` <= ( DATE_ADD(CURRENT_DATE(),interval -1 day) )
THEN
( these parenthesis are supurfluous.

CASE

WHEN `ConversionTypeName` like '%Sale%' THEN `Conversionsvalue`

END -- you forgot the end.

)
ELSE 0
END

)

don't overuse Parenthesis. It makes your code harder to read.

jaeW_at_Onyx · December 2020

lol... so now you need to use parenthesis to group your booleans. right now your tests are for the result of ...

date1

AND

date2

AND prod1

OR prod2

what this means is

(

date1

AND

date2

AND prod1

)

OR

prod2

in other words, you'll get the correct number if you're prod1 and between specific dates, OR if you're prod2 across all dates. so to get your desired result you MUST wrap your product checks in parenthesis.

date1

AND

date2

AND

(

prod1

OR

prod2

)

this returns, "it must be between dates AND it must be either prod1 OR prod2

CASE

WHEN

... test date1

AND ... test date2

AND (

...testProd1

OR testProd2

)

THEN doMath

END

problem is, doMath changes for different products because they have different conversion rates. You can't do what you're trying to accomplish with this data schema. The smarter way of handling this requirement be to add a column to your dataset called product_conversionRate that stores your conversion rate based on the product. this will simplify your beastmode.

but also ... do read my previous comment about separating beast mode requirements from date filter requirements.

Receiving an error in my Beastmode | Multiple case statements

Best Answer

Answers

Categories