+ Reply to Thread
Results 1 to 3 of 3

Excel 2007 : Two pivot table queries: How to add a cumulative column and a data sort query

  1. #1
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Two pivot table queries: How to add a cumulative column and a data sort query

    Hi,

    I've created a pivot table to tell me the monthly sales total by year and period. Is it possible to get the pivot table to create a calculated column that gives me the cumulative position? Also my pivot lists the data in the wrong order because of its format as the year and periods 2009/10-12 are above 2009/2-9. Is there any way to change the data sort rule so it recognises the correct order?

    I've attached an example of the pivot I am working on and an example of how I would like it to look.

    Thanks in advance,

    Snook
    Attached Files Attached Files
    Last edited by The_Snook; 04-27-2010 at 07:49 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-09-2009
    Location
    Liverpool, England
    MS-Off Ver
    W: 2019 H: 365
    Posts
    940

    Re: Two pivot table queries: How to add a cumulative column and a data sort query

    Me again,

    I'd be grateful of any reply to my query, even if it's just to say that what i'm looking to achieve is not possible.

    Regards,

    Snook

  3. #3
    Valued Forum Contributor
    Join Date
    08-23-2009
    Location
    Hampshire, UK
    MS-Off Ver
    Excel 2003, Excel 2007
    Posts
    328

    Re: Two pivot table queries: How to add a cumulative column and a data sort query

    To get the cumulative values, add the £ field to the pivot table again and change the Field Settings for this to Show values as Running Total in Yr/Pd.

    If you want the Yr/Pd to sort correctly I think you need to add zeros to the single digit months. I've added a column to your data using the formula

    =LEFT(A2,5)&TEXT(MID(A2,SEARCH("/",A2)+1,2),"00")

    which makes all the months 2 digits. I've then used this column in the pivot table.
    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