Aggregated and Non Aggregated - Index Calculation

cmarino
cmarino Member
in Beast Mode

Hello!

I am attempting to create an index out of two beast modes. One is an aggregated measure that returns the percent share of impressions against the total group. The second is a count of assets to capture how many social videos have been published per each social platform. For example, I'll have two columns for TikTok that tells me the x% share of total impressions and % share of posted videos across all our social platforms.

My issue is I'm I try to calculate those measures into an index. Text to SQL doesn't seem to return anything that actually validates or even returns what I'm looking for. I attempted the below calculations. Would anyone know what is wrong with my formula? Also, if this is even possible to calculate in a beast mode rather than integrating in the data flow?

% Impressions Share: IFNULL(sum(LFM_CONTENT_IMPRESSIONS_V8)/ NULLIF(SUM(SUM(LFM_CONTENT_IMPRESSIONS_V8)) over (), 0), 0)

% Posts Share: IFNULL(sum(Post Count)/ NULLIF(SUM(SUM(Post Count)) over (), 0), 0)

Index calculation I attempted: (
IFNULL(sum(LFM_CONTENT_IMPRESSIONS_V8)/ NULLIF(SUM(SUM(LFM_CONTENT_IMPRESSIONS_V8)) over (), 0), 0) +
IFNULL(sum(Post Count)/ NULLIF(SUM(SUM(Post Count)) over (), 0), 0)

*100)

Tagged:

Answers

  • brycec
    brycec Domo Employee

    What do you mean by "index" exactly? I don't understand how you are using that term.

    In regards to the formula you tried, I find that Beast Mode editor doesn't like it when you use non-windowed aggregation functions with windowed aggregation functions. So, you might have to try something like this:

    (
    
IFNULL(SUM(SUM(LFM_CONTENT_IMPRESSIONS_V8)) OVER () / NULLIF(SUM(SUM(LFM_CONTENT_IMPRESSIONS_V8)) OVER (), 0), 0) +
    
IFNULL(SUM(SUM(Post Count)) OVER ()/ NULLIF(SUM(SUM(Post Count)) OVER (), 0), 0)

    
*100)

    I also haven't messed around with an empty OVER clause, so that could be an issue too but I'm not sure.

    Was this comment helpful? Click Agree or Like below.
    Did this comment solve your problem? Accept it as the solution!

Categories