+ Reply to Thread
Results 1 to 13 of 13

running totals in Pivot Table - removing a user from the totals for the current week

  1. #1
    Registered User
    Join Date
    08-31-2013
    Location
    Bethesda, MD
    MS-Off Ver
    Excel 2007
    Posts
    9

    running totals in Pivot Table - removing a user from the totals for the current week

    Hi I am using running totals in my pivot table. We are tracking how many mobile devices are active. I use it for weekly reports. I don't copy all users each week - I simply add the new ones. For example, on July 26 we had 1,000 users (this is the first week we started tracking everyone that had registered in 2013). Then an additional 200 for Aug 2 (total 1,200), an additional 300 for Aug 9 (total 1,500), an additional 500 for Aug 16 (total 2,000). The question becomes - since I am not copying all users for each week - it would eventually become too many rows - how do I show when a user leaves the company? Specifically, reducing the week they left by one user. Versus deleting their name from the week they became active and having the numbers for that week - say Aug 2nd go down by one. I would really want it to be showing as one less in the Aug 23rd report.

    Thanks.
    Gayenell

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,250

    Re: running totals in Pivot Table - removing a user from the totals for the current week

    Perhaps have an indicator in your "database" to show ythat they have left, and then include that indicator in your PT and filter on that?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    08-31-2013
    Location
    Bethesda, MD
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: running totals in Pivot Table - removing a user from the totals for the current week

    FDibbins, thank you for taking a look. I tried to add another column to the data and use it in the pivot table, but could not figure out a way to make it work. Attached is sample data. For corporate my weekly running totals should be:
    7/26/2013 72
    8/02/2013 73
    8/09/2013 74
    8/16/2013 75
    8/23/2013 74

    I could not figure out how to get the 8/23/2013 row to reduce the corporate total by 1. Instead my corporate total added the deleted 1 to make a total of 76. I know it must be a simple change, but can not figure out how to make the pivot do the subtraction.

    Instead my pivot creates this (which is probably better viewed in the attached spreadsheet):

    Corporate Corporate Total
    Active Left
    72 0 72
    73 0 73
    74 0 74
    75 0 75
    75 1 76
    Attached Files Attached Files

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,547

    Re: running totals in Pivot Table - removing a user from the totals for the current week

    Hi,

    And if you entered negative values in the Left column of your source data (e.g. -1 if one person left), would that correct your totals to the desired values in the Pivot?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Registered User
    Join Date
    08-31-2013
    Location
    Bethesda, MD
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: running totals in Pivot Table - removing a user from the totals for the current week

    Thank you for the idea. I did try a new column with 1's except where a user is deleted - I entered a -1. The problem becomes running totals - needed to drive the chart - if I don't include that column in my pivot table as a row or column label - then the pivot shows N/A. I have attached the newer version of the spreadsheet with the suggestion in case there is a way to play with it to get it to work.
    The chart definitely needs running totals for each week.
    Attached Files Attached Files

  6. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,547

    Re: running totals in Pivot Table - removing a user from the totals for the current week

    Hi,

    Not sure I understand. In this new version, right-clicking on one of the entries in the Corporate field, Show Values As/Running Total In/Base Field = Report.

    Is that not what you want?

    Regards

  7. #7
    Registered User
    Join Date
    08-31-2013
    Location
    Bethesda, MD
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: running totals in Pivot Table - removing a user from the totals for the current week

    Hi, I do want running totals for BYOD and Corporate using the row label of "report". But to get the column to include the subtraction of a device I have added "Active" Since it is not in the pivot table - I get N/A. I have tried putting Active under the column labels, but still no correct running table/chart. Please see the word doc with screenshots to see the variations on what I have tried, perhaps that would help.
    Attached Files Attached Files

  8. #8
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,547

    Re: running totals in Pivot Table - removing a user from the totals for the current week

    I'm really sorry, but I still don't understand.

    In your last Excel attachment, doing precisely as I said in my previous post seems to give you what you want. No #N/As, and running totals, with a drop to 74, not 76, in the final row.

    Apologies if I'm not fully understanding.

    Regards

  9. #9
    Registered User
    Join Date
    08-31-2013
    Location
    Bethesda, MD
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: running totals in Pivot Table - removing a user from the totals for the current week

    I so apologize. I feel as if I am the one being dense. Would it be possible for you to attach the spreadsheet. With the running totals recogniznig the decline for Week Aug 23rd. I seem to be missing the point. Thanks.

  10. #10
    Registered User
    Join Date
    08-31-2013
    Location
    Bethesda, MD
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: running totals in Pivot Table - removing a user from the totals for the current week

    Actually, I may get where we are disconnecting. The running totals would be each week. Aug 2nd would be 73 for Corporate, Aug 9 is 74, Aug 16 is 75 and Aug 23 would be 74. I really would not need a grand total row. Is that where we are tripping each other up?

  11. #11
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,547

    Re: running totals in Pivot Table - removing a user from the totals for the current week

    Attached.

    Regards
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    08-31-2013
    Location
    Bethesda, MD
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: running totals in Pivot Table - removing a user from the totals for the current week

    Your right - yours looks perfect. I will disect it to figure out the differences. Thank you so much for helping. I will mark the problem solved. I really appreciate all your help.

  13. #13
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,547

    Re: running totals in Pivot Table - removing a user from the totals for the current week

    You're very welcome. And get back to me if you need any more help with this.

    Regards

+ 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] Pivot Table with Running totals not working
    By eisenbergg in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-01-2013, 09:47 AM
  2. Replies: 1
    Last Post: 03-08-2012, 05:36 PM
  3. Tally sheet, reseting parts once a week but keep running totals
    By MDCK in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-15-2008, 04:22 AM
  4. Pivot Running Totals
    By money_man in forum Excel General
    Replies: 2
    Last Post: 01-31-2007, 04:23 AM
  5. How do I do running Totals in a Pivot Table
    By Pivot Freak in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-07-2006, 01:35 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