+ Reply to Thread
Results 1 to 6 of 6

Excel 2010 Pivot table

  1. #1
    Registered User
    Join Date
    03-27-2012
    Location
    PERTH, AUSTRALIA
    MS-Off Ver
    excel 2010
    Posts
    3

    Red face Excel 2010 Pivot table

    Hi I have a data source with a unique identifier, created date, resolved date, current status, etc. How do I create a pivot table/chart that shows the volume create and resolved monthly for a particular period (say July 2011 to March 2012)?

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Excel 2010 Pivot table

    Hi Moe, welcome to the forum.

    I would think you could drag the Resolved Date field to the Row Headers section, then drag the Unique Identifier field to the Value section to get a count by resolved date. Then click any of the Resolved dates and choose Group.. and select Month. (Deselect any others by clicking on them to remove the highlighting.)

  3. #3
    Registered User
    Join Date
    03-27-2012
    Location
    PERTH, AUSTRALIA
    MS-Off Ver
    excel 2010
    Posts
    3

    Re: Excel 2010 Pivot table

    Thanks for the response, Paul. I need both the created and resolved on the same chart. What I failed to state previously is there is a data connection and if the resolved date is null, my data connection inserts "1/01/1970 8:00:00 AM" so by counting it, the count is incorrect.

  4. #4
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Excel 2010 Pivot table

    Perhaps then:

    Assuming your data is in A2:D__, in E1 put a new header like Resolved2, then in E2 use the formula:

    =IF(D2="",NA(),D2)

    Fill that down. Create the PT and drag the Resolved2 field to the Row Headers section, and Unique ID field to the Values section. (Change from Sum to Count, of course.) Finally, click the drop-down on the Resolved2 field header and select Filter. Un-check the entry for #NA and your totals should be correct.

    Hopefully that works!

  5. #5
    Registered User
    Join Date
    03-27-2012
    Location
    PERTH, AUSTRALIA
    MS-Off Ver
    excel 2010
    Posts
    3

    Re: Excel 2010 Pivot table

    I've attached a sample file. It includes a pivot table which is not what I am after. What I need is to show the volume of calls that were created by Year/Month (easy) and the calls resolved by Year/Month. The calls resolved volume is to be regardless of when the call was created.

    Thank you in advance.
    Attached Files Attached Files

  6. #6
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: Excel 2010 Pivot table

    Hmm, having a tough time trying to get that to work.. it may just be easier to use a summary table. See attached (chart not updated, but summary table is on Data sheet.)

    Your original data is in columns A:E. In H1-K1 put the headers Year, Month, Num Created, Num Resolved. In column H put 2010 (12 times), 2011 (12 times) and 2012 (again, 12 times). In cell I2 put 1/1/2010 (Jan 1, 2010), then in I3 put 2/1/2010 (Feb 1, 2010). Use Fill > Series (or the Fill Handle) to fill down through 2012 (the first of each month should appear).

    In J2 use the formula:

    =SUMPRODUCT(--(YEAR($C$2:$C$1000)=H2),--(MONTH($C$2:$C$1000)=MONTH(I2)))

    In K2 use:

    =SUMPRODUCT(--(YEAR($E$2:$E$1000)=H2),--(MONTH($E$2:$E$1000)=MONTH(I2)))

    Fill those down for each year/month combination, and then build your chart off of that data.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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