1 Reply Latest reply: Aug 11, 2016 3:26 AM by Gysbert Wassenaar

# Attributing Revenue calculated Quarterly with Set Analysis to Sub Months

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

Perhaps something like this:

Revenue contribution:

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