2 Replies Latest reply: Jul 3, 2017 11:53 AM by Gavin Kite RSS

    Aggr Advice Required

    Gavin Kite

      Hi

       

      I'm looking for some help on Aggr. I thought i had a pretty good knowledge of Aggr but this has stumped me!

       

      I have total sales by MM - YY and I have discounts, as you can see from the table below, apart from the date, the 2 numbers aren't related in the data model

       

      Current Position

       

      Month YearDiscount TypeDiscount AmountTotal SalesDiscount %
      Jan 2017--10,000-
      Jan 2017Coupon500--
      Jan 2017Staff1000--
      Jan 2017Till Discount200--
      Feb 2017--20000-
      Feb 2017Coupon300-
      Feb 2017Staff600-
      Feb 2017Till Discount500--

       

      What I'd like to do is to work out at discount type level, what percentage of discount has been given as a percentage of total sales. Feels like this should be achievable with Aggr but I'm not having much luck

       

      Required Solution

      Month YearDiscount TypeDiscount AmountTotal SalesDiscount %
      Jan 2017--10,000-
      Jan 2017Coupon50010,0005%
      Jan 2017Staff100010,00010%
      Jan 2017Till Discount20010,0002%
      Feb 2017--20,000-
      Feb 2017Coupon30020,0002%
      Feb 2017Staff60020,0003%
      Feb 2017Till Discount50020,0003%

       

       

      My attempt so far has been

       

      Exp 1

      Sum ([Discount Amount])

      /

       

      Sum(Aggr(Sum([Total Sales]),[Month Year])

       

      Exp 2

      Sum ([Discount Amount])

      /

       

      Sum(Total Aggr(Sum([Total Sales]),[Month Year])

       

      Can anyone help me out?

       

      Thanks