DOMO SQL - Formula

EMart · December 19

Hi Team,

The formula below is validating but it is showing "NaN" for all CPM values. For context estimated_youtube_ad revenue and ad_impressions may include the following values: 0, Null values, integers greater than 0. Any potential way to fix this? Thank you in advance!

Select*

, sum("ESTIMATED_YOUTUBE_AD_REVENUE")/(sum("AD_IMPRESSIONS")/1000)NOTNULL as "CPM"

ArborRose · December 19

The NaN means "not a number". LIkely due to a divide by zero issue. Maybe try catching the condition

SELECT *,

    CASE 

        WHEN sum("AD_IMPRESSIONS") IS NULL OR sum("AD_IMPRESSIONS") = 0 THEN NULL

        ELSE sum("ESTIMATED_YOUTUBE_AD_REVENUE") / (sum("AD_IMPRESSIONS") / 1000)

    END AS "CPM"

EMart · December 19

This is helpful, thank you! Is there any way to include it in the Select portion? Similar to the following:

SELECT *, 'YouTube' as Platform,
"MONTH" as "Date"
, sum("ESTIMATED_YOUTUBE_AD_REVENUE")/(sum("AD_IMPRESSIONS")/1000)NOTNULL as "CPM"

from "youtube_monthly"

ArborRose · December 19

Yes, you can put CASE statements in the SELECT or in the criteria (WHERE clause).

EMart · December 19

I tried testing the formula as suggested but it's not working on my end, any idea of another potential workaround?

ArborRose · December 19

You could use COALESCE to make sure the sum of the estimated youtube ad revenue does not return NULL.

SELECT *,

    CASE 

        WHEN SUM("AD_IMPRESSIONS") IS NULL OR SUM("AD_IMPRESSIONS") = 0 THEN 0

        ELSE COALESCE(SUM("ESTIMATED_YOUTUBE_AD_REVENUE"), 0) / (SUM("AD_IMPRESSIONS") / 1000)

    END AS "CPM"

FROM "youtube_monthly"

DOMO SQL - Formula

Answers

Categories