Case within case in beast mode

Hello, can someone help me with an issue with beat mode - I am able to successfully validate the formula in the beast mode, however when I use the calculated column to the chart, it throws an erro - would you'll know why this happens?

 

(CASE when DAYNAME(WEEKDAY(DATE(`itm_snapshot_date`)))IN ('Saturday') and SUM(`ext_sls_a`)/ COUNT((CASE when `listing_status`='APPROVED' AND `inventory`=0 then 'Live OOS items' when `listing_status`='APPROVED' AND `inventory`>0 then 'Live Purchasable items' else 'Pending, Rejected, Suspended' end)) then 'Prod' else 'No' end )

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    There's a lot of ... not great things going on here.

     

    DAYNAME(WEEKDAY(DATE(`itm_snapshot_date`)))

    This is a really verbose way to get the name of the day.  I recommend looking at the DATE_FORMAT function to simplify.  

    Also DayName is functionally the same as Weekday, so use one or the other, but don't run your function through both.  Also, look at the definition of Dayname, it expects a Date column.  You are passing it an integer (weekday)

     

     

    ... (DATE(`itm_snapshot_date`)))IN ('Saturday')  using IN instead of = is less efficient during query execution.  You just have one value so Weekday = "Saturday" will be faster.

     

    THE ROOT PROBLEM

    WHEN

    CASE when DAYNAME... = ('Saturday') and SUM(`ext_sls_a`) / COUNT() > 0 THEN 'Prod' ELSE 'No'

    END

     

    so you're jumping in and out of aggregated data in the same beast mode.  

    If you just had a dataset with 1M rows that aggregates down to 10 rows and you write

    CASE when DAYNAME... = ('Saturday') THEN 'Prod'...

    then you'd evaluate your function at the row level (before aggregations like SUM have been applied to a dataset.

     

    When you write 

    CASE WHEN SUM(`ext_sls_a`) / COUNT() > 0 THEN 'Prod' 

    Then you're performing the CASE operation on the results of an aggregated dataset SUM()s and COUNTs().  In other words, you're CASE statement is being evaluated against the 10 row aggregation.

     

    What you can't do is you can't combine the math on Weekday and SUM() because they exist in different states of the data (pre / post aggregation).

     

    WHAT YOU MUST DO

    wrap date in a MAX() function. CASE when WEEKDAY(DATE(MAX(`itm_snapshot_date`))) = 7 ...

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    There's a lot of ... not great things going on here.

     

    DAYNAME(WEEKDAY(DATE(`itm_snapshot_date`)))

    This is a really verbose way to get the name of the day.  I recommend looking at the DATE_FORMAT function to simplify.  

    Also DayName is functionally the same as Weekday, so use one or the other, but don't run your function through both.  Also, look at the definition of Dayname, it expects a Date column.  You are passing it an integer (weekday)

     

     

    ... (DATE(`itm_snapshot_date`)))IN ('Saturday')  using IN instead of = is less efficient during query execution.  You just have one value so Weekday = "Saturday" will be faster.

     

    THE ROOT PROBLEM

    WHEN

    CASE when DAYNAME... = ('Saturday') and SUM(`ext_sls_a`) / COUNT() > 0 THEN 'Prod' ELSE 'No'

    END

     

    so you're jumping in and out of aggregated data in the same beast mode.  

    If you just had a dataset with 1M rows that aggregates down to 10 rows and you write

    CASE when DAYNAME... = ('Saturday') THEN 'Prod'...

    then you'd evaluate your function at the row level (before aggregations like SUM have been applied to a dataset.

     

    When you write 

    CASE WHEN SUM(`ext_sls_a`) / COUNT() > 0 THEN 'Prod' 

    Then you're performing the CASE operation on the results of an aggregated dataset SUM()s and COUNTs().  In other words, you're CASE statement is being evaluated against the 10 row aggregation.

     

    What you can't do is you can't combine the math on Weekday and SUM() because they exist in different states of the data (pre / post aggregation).

     

    WHAT YOU MUST DO

    wrap date in a MAX() function. CASE when WEEKDAY(DATE(MAX(`itm_snapshot_date`))) = 7 ...

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"