topic Nested aggregation and set analysis + TOTAL? in QlikView

Nested aggregation and set analysis + TOTAL?

Mon, 09 Mar 2015 10:50:06 GMT

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

Re: Nested aggregation and set analysis + TOTAL?

anbu1984 — Mon, 09 Mar 2015 11:23:00 GMT

Can you post sample data and expected output

Re: Nested aggregation and set analysis + TOTAL?

Mon, 09 Mar 2015 13:08:05 GMT

Hi,

find attached a test version.

Looking forward to your reply.

Best

Sebastian

Re: Nested aggregation and set analysis + TOTAL?

anbu1984 — Mon, 09 Mar 2015 13:25:31 GMT

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

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

Re: Nested aggregation and set analysis + TOTAL?

Mon, 09 Mar 2015 13:31:48 GMT

Hi Anbu,

thanks for your help! You solved my challenge

Best

Sebastian