Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009

    Unanswered: Grouping Dates in a report

    I created a report that shows the sales order for a given date but I want to know how to create a group that will show sales orders that were completed in May 2008, June 2008, July 2008, etc. Once I able to group the orders for the month then I would like to sum the revenue for the sales orders that were completed in that month. Here is an example:

    SalesOrder InstallComplete Revenue
    640000001 5/1/08 500.00
    640000002 5/10/08 1000.00
    640000003 5/15/08 1500.00
    640000004 5/20/08 550.00
    640000008 6/1/08 200.00
    640000009 6/16/08 1009.00
    640000010 7/1/08 5004.00

    Group all the orders for May together.
    Group all the orders for June together.

    Any help will be appreciated.

  2. #2
    Join Date
    Sep 2008
    London, UK

    Here is one way, supposing your example data is in the range A1:C8.

    Step 1 - Set up a helper column:
    We will use a helper column in column D.
    Add the field name "Month" into D1.
    In D2, place this formula


    Then fill this formula down to D8.

    Select cells D2 : D8, right-click | Format.
    Change the format to custom: mmm-yy

    Step 2 - Create A Pivot Table
    Now the helper column is set up we will create a pivot table to derive the monthly totals.

    Select the range A1 : D8
    Data | PivotTable
    Click on Next
    Make sure the range A1 : D8 address is populated in the RefEdit control.
    Click on Next
    Choose Existing or New worksheet - whichever you need.
    Click on Finish

    From the pivot table field list drag "Month" into the "Drop Row Fields Here" section of the pivot table.
    From the pivot table field list drag "Revenue" into the "Drop Data Items Here" section of the pivot table.

    Amend the field/table settings as necessary.

    Hope that helps...

  3. #3
    Join Date
    Nov 2004
    or ...
    use colin L option 1
    but instead of a pivot table (don't get me wrong i love pivot tables)
    but you could...
    under the data menu - add subtotals which will group as well and add a sum total to revenue column based on the monthyear.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts