Nested aggregation and set analysis + TOTAL?

[#1]

Hi everyone,

I would like to show the average Open rate of an email campaign (unique) and benchmark it with the average Open rate pver all email campaigns (unique).

So far the calculation of the individual email campaign works.

This is the formula for the individual campaign:

avg(aggr(count({<resource_type_id={67},Trackingtype_id={19}>}DISTINCT %profile_id)

/sum(recipients),%resource_id))

with resource_type_id and Trackingtype_id filtering for Activities that belong to an email campaign and that describe the email opening. The %recource_id is the dimension for the different email campaigns.

This part works.

However, the benchmark based on the overall average on all campaigns does not work. Following the formula that I have come up with so far. I marked the changes compared to the formula above in bold:

avg({1}total aggr(count ({1<resource_type_id={67}, Trackingtype_id={19}>}DISTINCT %profile_id)

/sum(recipients), %resource_id))

Unfortunately it gives me the same result as the first formula. But I would like to get a fix number that does not change when I change my selection, i.e. this benchmark figure should be indepenten of the current selection.

Can you help me out here?

Thanks in advance

Sebastian

[anbu1984]

Can you post sample data and expected output

[#3]

Hi,

find attached a test version.

Looking forward to your reply.

Best

Sebastian

[anbu1984 · Accepted solution]

=avg({1}total aggr(count ({1<resource_type_id={67}, Trackingtype_id={19}>}DISTINCT %profile_id)

/sum({1}recipients), %resource_id))

[#5]

Hi Anbu,

thanks for your help! You solved my challenge

Best

Sebastian