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

    Attributing Revenue calculated Quarterly with Set Analysis to Sub Months

    Russell Bywaters



      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


      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


      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?