14 Replies Latest reply: Mar 28, 2018 7:19 PM by Roger Bielicke

# Aggregation Help

Problem to solve.  Ad Groups rows are like "All Star Podiatrist Near Me" and "Podiatrist St. Louis" are showing in both tables.  This is due to to formula currently looking at each row of data, where I prefer to aggregate the all the rows for each Ad Group and assign to only one table.  Here's the current formula for CTR.  How do I update to aggregate for the total CTR for each Ad Group rather than each individual row of data?  As a bonus, how do I make the "CTR=(<"0.96")" into the Aggregate Avg CTR so automatically updates with data refresh.  Bonus items is lower priority.

=sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>}[Clicks])

/sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>}[Impressions])

• ###### Re: Aggregation Help

Not sure I understand, what is the expected output here? Which table should "All Star Podiatrist Near Me" appear in?

• ###### Re: Aggregation Help

I think you're wanting to use Aggr() to roll up the measures by Ad Group and have an overall Average?

Sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>} Aggr(sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>}[Clicks]),[Ad Group])

/Sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>} Aggr(sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>}[Impressions]),[Ad Group])

• ###### Re: Aggregation Help

Yes, David, trying to use the Aggr() function.  Now when I tried to copy the entire formula you proposed I get an error expression.  When I take just the part starting from Aggr it states OK but returns no values.  First, is it supposed to be the entire expression or just the portion starting from Aggr?  Second, any thoughts what may be throwing it off?

Much appreciate the help.

• ###### Re: Aggregation Help

sorry missing a paren at the end of each

Sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>} Aggr(sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>}[Clicks]),[Ad Group]))

/Sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>} Aggr(sum({<Brand={'Non Brand'},Time={'CY'},CTR={"<.096"},[Conv. rate]={">=.05"}>}[Impressions]),[Ad Group]))

As I dont have your data, hard to say. Assuming the original expression was correct and the the field to group by is called [Ad Group].

I usually break things down and solve one formula at a time, gradually adding/evaluating each set expression one at a time to see where an error might be. Syntax checker doesn't always catch things in set expressions.

• ###### Re: Aggregation Help

Much appreciate the help and the step by step approach David.  Okay, I was able to get aggregation step to work with

resulting in the following which is accurate.

Now I want to take the next step and only include those where the Aggregate CTR is >=5%, thus the final result would only be the 3 yellow highlighted rows below.  Note below is pivot table aggregation of many rows of data for each.  Assuming need another Aggr() formula but I'm guessing it would not be simply on CTR but sum version of Aggr(Clicks,AdGroup) /Aggr(Impressions,AdGroup) >=.05%.

Any thoughts or suggestion much appreciated.

• ###### Re: Aggregation Help

May be use an if?

If (

0

)

• ###### Re: Aggregation Help

Thanks Asisdhar.   I tried the following - tweaked a bit to simplify - but am getting "error in expressions" message.  Can you tell what's off in the formula?

I would expect the result to pull the below with only the top 2 rows with impressions > 10,000.  If I can get this view to work, I can then apply at more complex level for the CTR > 5%.  Thanks for your help.

• ###### Re: Aggregation Help

Looks like you're missing a comma before the 0 at the end:

• ###### Re: Aggregation Help

Yes, that fixes the expression.  Now that provides the following result, which is almost there.  How do I know show the only the aggregate rows that meet the IF = True?  Rather than listing with the other rows with = 0, goal is to simply exclude them from the view and only the top 2 rows would show.

Here's current formula  and current result.

• ###### Re: Aggregation Help

remove the 0 from the if, don't provide the false and it will return null.

• ###### Re: Aggregation Help

David - Thanks for the quick reply.  That changes the 0 to a "--" and the rows still there.  How do I approach so that those rows do show in the visual?   Thanks,

• ###### Re: Aggregation Help

drat. so, need to put the "if" into the set expression Something like:

variable vImpressions =

variable vTopImpressions =

might need

• ###### Re: Aggregation Help

unchecking 'Include Zero Value' under Add-ons -> Data Handling should remove 0s

• ###### Re: Aggregation Help

Thank you all very much.  You helped me reach the end game.  Here is example of formula and final output where only showed those Ad Groups with Aggregate CTR > 5% and Conv Rate > 9.6%.   Much appreciate the coaching.