Case within case in beast mode

Reply
Highlighted
White Belt

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 )


Accepted Solutions
Highlighted
Black Belt

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post


All Replies
Highlighted
Black Belt

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post

Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.