DOMO SQL - Formula

EMart

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

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

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

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

EMart

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

ArborRose

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"