Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

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 MonthDrop IDAdbook Price RevenueImpressionsDrop Wide Imps
Jan16520822017.811128.514361422842
Feb16520822017.81091610

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 MonthDrop IDAdbook Price RevenueImpressionsDrop Wide Imps
Jan16520822017.812017.81361422842
Feb16520822017.81091610
Mar16520822017.810100670

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

1 Reply
MVP & Luminary
MVP & Luminary

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])


talk is cheap, supply exceeds demand