Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Perhaps something like this:
Revenue contribution:
sum(total <[Drop ID]> Revenue) * sum([DFP Impressions]) / sum(total <[Drop ID]> [DFP Impressions])