DOMO SQL - Formula

EMart
EMart Member
in SQL DataFlows

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"

Tagged:

Answers

  • ArborRose
    ArborRose Coach

    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"

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • EMart
    EMart Member

    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
    ArborRose Coach

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

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • EMart
    EMart Member

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

  • ArborRose
    ArborRose Coach

    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"

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Categories