3 Replies Latest reply: Apr 26, 2012 3:52 AM by Ionut Biliuti RSS

    average monthly sales for the last 12 months

    Ionut Biliuti

      Hello,

       

      In a Pivot table I have to calculate the average monthly sales for the last 12 months for each customer, having these two tables and considering the first transaction date for each customer.

       

      [sales]:

      customer,net_sales,date

      AAA,100,2/13/2011

      AAA,100,6/14/2011

      AAA,100,7/25/2011

      AAA,100,9/13/2011

      AAA,100,12/17/2011

      AAA,100,4/14/2012

      BBB,100,10/25/2011

      BBB,100,11/16/2011

      BBB,100,12/27/2011

      BBB,100,4/18/2012

       

      [customer]:

      customer,first_transaction_date,

      AAA,4/13/2008,

      BBB,10/25/2011

       

      For AAA customer the average is 500/12 $/month because I have to exclude transactions older than 12 months.

      For BBB the average is 400/7 $/month (sales between first transaction date and current month)

       

      Any help will be greatly appreciated,

      Ionut