Receiving an error in my Beastmode | Multiple case statements

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

Best Answer

  • GrantSmith
    GrantSmith Indiana 🔴
    Accepted Answer

    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
    )

     

Answers

  • GrantSmith
    GrantSmith Indiana 🔴

    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)

     

     

  • 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 ?

  • 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
    GrantSmith Indiana 🔴

    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
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @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
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    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
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    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.