# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

## 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])

Tags (1)
14 Replies
MVP

## Re: Aggregation Help

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

Valued Contributor

## 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])

New Contributor II

## 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.

Valued Contributor

## 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.

New Contributor II

## 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.

Honored Contributor III

## Re: Aggregation Help

May be use an if?

If (

0

)

New Contributor II

## 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.

Valued Contributor

## Re: Aggregation Help

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