Nesting of aggregate functions ERROR

I want to use a SUM IF ELSE with a calculated field (Widget Impression Test) and a regular field (impressions).

My calculated field is sum(avg({widget_impressions}, [{metric_day},{marketplace_id},{device_type},{placement_transformed}]))

In the screenshot I have the SUM IF ELSE. I know it’s erroring out, but is there a solution to this? I have to create the calculated field as is.

Can you try to change your calculated field to use avgOver instead?

Widget Impression Test =
avgOver({widget_impressions}, [{metric_day},{marketplace_id},{device_type}, {placement_transformed}], PRE_AGG)

1 Like

Also, you don’t need to use the ifelse function 6 times. You can just use it once and put all your conditions in it.

1 Like

Thanks David!

So the SUM IF ELSE works now when using

Widget Impression Test = avgOver({widget_impressions}, [{metric_day},{marketplace_id},{device_type}, {placement_transformed}], PRE_AGG)

However numbers are not coming out correct.

In my screenshot Widget Impressions is what I just did

  1. Widget Impression Test = avgOver({widget_impressions}, [{metric_day},{marketplace_id},{device_type}, {placement_transformed}], PRE_AGG)
  2. Add that calculated field into the SUM IF ELSE, so now I can if else between impressions or widget Impressions.

Actuals is the correct number but it’s the calculated field as sum(avg({widget_impressions}, [{metric_day},{marketplace_id},{device_type},{placement_transformed}]))
which i cannot add into the SUM IF Else

Do you mean that impressions is a calculated field? Why do you need to put a sum around your avg when the sum ifelse is already doing a sum?

widget impressions is a calculated field, impressions is just a regular field. I want to SUM if else dependent on the placement transformed chosen.

I don’t need to put a SUM, but I can’t include a calculated field of avg({widget_impressions}, [{metric_day},{marketplace_id},{device_type},{placement_transformed}]) within the SUM Ifelse because I get a “The calculation operated on Level Aware Calculation (LAC) agg expressions is not valid”

Try calculating the 2 sums first and then wrapping them in the ifelse.

Test the 2 sums on their own to see if they give the correct result based on your conditions.

Total Widget Impression Test =
sum(avgOver({widget_impressions}, [{metric_day},{marketplace_id},{device_type}, {placement_transformed}], PRE_AGG))

Total Impressions = sum({impressions})

Then wrap them in the ifelse:

{placement_transformed} = ‘Bottom’, {Total Impressions},
{placement_transformed} = ‘3PSL’, {Total Widget Impressions Test},
{placement_transformed} = ‘DP’, {Total Widget Impressions Test},
{placement_transformed} = ‘other’, {Total Widget Impressions Test},
{placement_transformed} = ‘Sparkle’, {Total Impressions},
{placement_transformed} = ‘Inline’, {Total Impressions},

1 Like

@David_Wong The issue is not with impressions because that is a separate field, so doesn’t have to match.

Total Widget Impression Test =
sum(avgOver({widget_impressions}, [{metric_day},{marketplace_id},{device_type}, {placement_transformed}], PRE_AGG))

Old Total Widget Impression Test = sum(avg({widget_impressions}, [{metric_day},{marketplace_id},{device_type},{placement_transformed}]))

Old Total Widget Impression Test gives me the correct number, but errors in the SUM IF ELSE,
Total Widget Impression Test doesn’t match Old Total Widget Impression Test; however, it fits in the SUM IF ELSE

*When do I min/max they match, but SUM they don’t …

@David_Wong any idea?

It’s hard to troubleshoot without seeing your unaggregated data. Can you post a few rows of your unaggregated data and describe what you are trying to calculate with the widget impression field?

1 Like

To get widget impressions I am left joining to my base SQL on these criteria.

This is my base SQL

This is when I left join the Widget impressions on those criteria.

Now what I want to do is sum(impressions) on specific placement_transformed vice versa sum(widget impressions) on specific placement_transformed.

When I left join it’s obviously double counting the widget impressions, so that was why I had sum(avg({widget_impressions}, [{metric_day},{marketplace_id},{device_type},{placement_transformed}]))

Can you try this?

in({placement_transformed}, [‘Bottom’, ‘Sparkle’, ‘Inline’]), sum(avg({widget_impressions}, [{metric_day},{marketplace_id},{device_type},{placement_transformed}])),
in({placement_transformed}, [‘3PSL’, ‘DP’, ‘other’]), sum(impressions),

If there’s no error but the result is wrong, it could be that the partition in the calculated field is incorrect. Can you show me 2 duplicate rows in your data so that I can see if the partition that you’re using is correct?

Can you also show a screenshot of the visual where you’re using the above calculated field? The screenshot of your table visual is cut off, so I can’t see all your dimensions.

1 Like


Sorry, the field inside the avg function should be impressions.

in({placement_transformed}, [‘Bottom’, ‘Sparkle’, ‘Inline’]), sum(avg(impressions, [{metric_day},{marketplace_id},{device_type},{placement_transformed}])),
in({placement_transformed}, [‘3PSL’, ‘DP’, ‘other’]), sum(impressions),

1 Like

@David_Wong Ahh still doesn’t work.