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)
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.
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
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:
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},
null
)
@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?
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?
ifelse(
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),
null
)
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.
Sorry, the field inside the avg function should be impressions.
ifelse(
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),
null
)
@David_Wong Ahh still doesn’t work.
