+ Reply to Thread
Results 1 to 2 of 2

PivotTable and Date Query

  1. #1
    Registered User
    Join Date
    04-13-2015
    Location
    UK
    MS-Off Ver
    2010
    Posts
    3

    PivotTable and Date Query

    Hi all, i am new here so please be gentle - I have a spreadsheet which has essentially 3 columns i need to use:
    * Date Created
    * Due Date
    * Completed Date

    What i need to do is to create a bar graph showing the number of items (month on month total), average days overdue (overlay line), average allowed days to close (overlay line) - for the latter two, i have a calculated field which generates the days overdue using:

    Please Login or Register  to view this content.
    This serves its purpose and allows further analysis, to work out the days allowed to close i have used the same formula, using due date and date created instead.

    Sample date:

    Date Created | Due Date | Completed date
    01/01/2014 | 21/02/2014 | 21/01/2014
    01/01/2014 | 01/02/2014 | 10/02/2014
    01/01/2014 | 21/03/2014 | 12/03/2014
    01/01/2014 | 17/04/2014 | 01/04/2014


    The Graph theoretically needs to have:

    * X Axis: Months (Jan - Dec)
    * Primary Y Axis: Total items (for bar)
    * Secondary Y Axis: Average Days (for 2 line graphs)

    The total items should be a running month on month total ie. the total for Jan on the graph the bar should be 3 (i.e. total left form Feb - Dec), the total for Feb should be 2, the total for March should be 1 and the total for April should be 0. This is based on the Completed date and be a running total year to end.

    I need the ability to set a starting number, which will be the number of items December 2013 total, i think i can do this using the "Show Data as Difference From" option on the PivotTable, however i can't seem to see a way to set a starting total?

    I am using Excel 2010 on Windows 7.

    I am unable to upload a sample at the moment, however if needs be i can tomorrow.

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    Re: PivotTable and Date Query

    Please upload a sample workbook if possible.

    Based on what I can visualize, we can use a Pivot Table/Chart..

    Will be sure after seeing the sample workbook..
    Cheers!
    Deep Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] PivotTable Query
    By Dnyan in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 12-02-2013, 06:52 AM
  2. Pivottable query
    By danison in forum Excel General
    Replies: 1
    Last Post: 05-02-2011, 07:20 AM
  3. PivotTable query and overwriting existing data
    By rsilayan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-18-2008, 12:53 PM
  4. [SOLVED] Create Excel PivotTable that uses Access query as external data so
    By alexhatzisavas in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-30-2005, 04:05 AM
  5. How to use ADO to change Pivottable sourcedata query
    By Chace in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-17-2005, 02:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1