Attributing Revenue calculated Quarterly with Set Analysis to Sub Months

Wed, 10 Aug 2016 14:31:47 GMT

Hello,

I have a current expression that works for a rolled up "KPI" chart object in QlikSense but is not suitable for a monthly bar chart.

The expression is...

sum(aggr((sum([DFP Impressions])/sum({1<[Drop ID]>}[DFP Impressions])*[Adbook Price]),[Drop ID]))

A straight table that mimicks the problems I face gives the following output for Jan and Feb

DFP Reporting Month	Drop ID	Adbook Price	Revenue	Impressions	Drop Wide Imps
Jan	1652082	2017.81	1128.514	3614	22842
Feb	1652082	2017.81	0	9161	0

The Drop Wide Imps are calculated by...

sum(aggr(sum({1<[Drop ID]>}[DFP Impressions]),[Drop ID]))

which you will notice is also used in the main revenue calculation.

The point is for a "KPI" chart object a revenue figure of £1128 is fine but for a bar chart all of it is attributed into January and zero into February.

If I choose all 3 months of the quarter I get a similar scenario and again my bar chart using the same equation puts all revenue into January.

DFP Reporting Month	Drop ID	Adbook Price	Revenue	Impressions	Drop Wide Imps
Jan	1652082	2017.81	2017.81	3614	22842
Feb	1652082	2017.81	0	9161	0
Mar	1652082	2017.81	0	10067	0

So the question is...

How can I use Drop Wide Imps (aggregated at Drop ID level) with month by month impression data to give the revenue contribution of individual months?

Thanks

Russell

Re: Attributing Revenue calculated Quarterly with Set Analysis to Sub Months

Gysbert_Wassenaar — Thu, 11 Aug 2016 07:25:19 GMT

Perhaps something like this:

Revenue contribution:

sum(total <[Drop ID]> Revenue) * sum([DFP Impressions]) / sum(total <[Drop ID]> [DFP Impressions])

topic Re: Attributing Revenue calculated Quarterly with Set Analysis to Sub Months in App Development

Attributing Revenue calculated Quarterly with Set Analysis to Sub Months

Re: Attributing Revenue calculated Quarterly with Set Analysis to Sub Months