Aggregated and Non Aggregated - Index Calculation

cmarino

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)

brycec

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.