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

      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