Null Values Beastmode

imelendez

Good evening, Community,

I am creating an app that will be pulling data down from Domo and I seem to be running into some issues with some beast mode logic.

The beast mode (Open Rate): 

COUNT(CASE WHEN `activityName` = 'Open Email' THEN `activityDate` END) / COUNT(CASE WHEN `activityName` = 'Send Email' THEN `activityDate` END)

The issue:

When you look at the result in the card it compiles fine. When I try to call it via the API and write it to MySQL I run into a “Data Truncated” issue because a null value is trying to be passed for some rows.

Time frame I am testing and store #:

JULY 2021 (entire month) and Mission Bend #001 and The Woodlands #002 (store showing null value for open_rate)

What I have tried:

I believe is the issue is due to some counts not resulting with any values and null is put. The way I came to this conclusion is I broke down the above formula into two columns (separate fields) and can see the counts are correct for the top and bottom part of the formula for Mission Bend, but for other stores, the bottom part you will get a Null / Blank value. I tried to change the formula on my end to COUNT(CASE WHEN `activityName` = 'Send Email' THEN `activityDate` ELSE 0 END) and while the error goes away the data is incorrect. The result value I should be seeing is 33.2% but I am getting 8.5%. Is there a better way to refactor this way so that the count is correct when you put the formula together: expected outcome should be top = 319 / bottom = 960 = 33.2%?

Below is a screenshot of the store that shows a null value for the open rate column, where as the first store I was testing actually has values in there.

I appreciate your help in advance.

MarkSnodgrass

Try doing this for your beast mode to get the counts you want:

SUM(CASE WHEN `activityName` = 'Open Email' THEN 1 ELSE 0 END) / SUM(CASE WHEN `activityName` = 'Send Email' THEN 1 ELSE 0 END)

This should eliminate any issues with nulls or blanks

GrantSmith

one addition to @MarkSnodgrass answer is to wrap it in a case statements to handle the situation where you don’t have any send email activities (your denominator is 0)