Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Screen Shot 2018-03-20 at 9.20.44 PM.pngScreen Shot 2018-03-20 at 9.21.00 PM.png

14 Replies

Re: Aggregation Help

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

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

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

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

rbielicke
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

=Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])

resulting in the following which is accurate.

Screen Shot 2018-03-24 at 1.57.21 PM.png

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

Screen Shot 2018-03-24 at 2.05.29 PM.png

Any thoughts or suggestion much appreciated.

sasiparupudi1
Honored Contributor III

Re: Aggregation Help

May be use an if?

If (

Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])>=.05,

Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])

0

)

rbielicke
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?

=If (Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])>=10,000,

Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])0)

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.Screen Shot 2018-03-28 at 12.01.41 PM.png

dwforest
Valued Contributor

Re: Aggregation Help

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

=If (Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])>=10,000,

Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group]),0)

rbielicke
New Contributor II

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.

=If (Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group])>=10000,

Aggr(sum({<Brand={'Non Brand'},Time={'CY'}>}[Impressions]),[Ad group-Sheet1-2.Ad group]),0)Screen Shot 2018-03-28 at 12.47.17 PM.png

Community Browser