2 Replies Latest reply on Jan 24, 2013 12:33 PM by Kevin Purcell

    Sales from twelve month prior to last order date

    Kevin Purcell

      Hello,

       

      I am looking to run a report that will provide me a list of customer's last order date was prior to a filtered date and also return the previous twelve months sales for each customer paste on the same last order date.

       

      For example, I the list of customers I would like to return is based on the following:  [Last Order Date] was prior to 6/30/2012 and for each customer, provide their trailing twelve month sales based on each individual customer's last order date.  Sample data:

       

      Customer NumberLast Order DateSales Order DateItemSalesOrder #
      John1/1/20122/1/2010Bike $        100          80,420
      John1/1/20124/6/2011Bike $        100          81,278
      John1/1/20125/2/2011Bike $        100          81,330
      John1/1/20126/1/2011Bike $        100          81,390
      John1/1/20126/15/2011Bike $        100          81,418
      John1/1/20126/21/2011Bike $        100          81,430
      John1/1/20127/10/2011Bike $        100          81,468
      John1/1/201210/16/2011Bike $        100          81,664
      John1/1/20121/1/2012Bike $        100          81,818
      Phil12/1/201211/1/2012Truck $          50          82,428
      Phil12/1/201211/15/2012Truck $          50          82,456
      Phil12/1/201211/28/2012Truck $          50          82,482
      Phil12/1/201212/1/2012Truck $          50          82,488
      Results
      Customer NumberLast Order DateTrailing Twelve Month Sales
      John1/1/2012 $                        800
      The 2/1/10 order would be excluded
      Phil had sales after 6/30/12 cutoff

       

      Would I be able to use the datediff function with the field [Last Order Date] and the 6/30/12 date?

       

      Thank you for any assistance you could provide.

       

      Kevin