+ Reply to Thread
Results 1 to 5 of 5

unable to get pivot table to display Running Total In correctly across a year end

  1. #1
    Registered User
    Join Date
    08-07-2014
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    24

    unable to get pivot table to display Running Total In correctly across a year end

    Hi,
    I attached an example (with random data) of the problem I fail to resolve.
    The Data Sheet has data sorted by dates running from 1 Jul 17 to 28 Feb 18.
    In the Cash Flow Projection sheet I wish to display accumulated estimated outflows compared to accumulated estimated inflows over the relevant period of 8 months in a simple line chart. For this I used "Running Total In" on the "Show Values As" tab under the "Value Field Settings" to give me the accumulating data values.
    I get it to display correctly from Jul to Dec, but it starts calculating anew from Jan to Feb. The chart shows what I mean.
    I have fiddled around, but cannot seem to get it right. One option was to remove Years from the Axis, but then Jan & Feb 18 shows before Jul to Dec 17 on the chart that confuses a viewer.
    Could some kind person please point me to what I fail to see/do?
    Thank you kindly.
    A
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,546

    Re: unable to get pivot table to display Running Total In correctly across a year end

    Not the most elegant solution, however I think that it yields the result that you want.
    This proposed solution adds two helper columns,CEE (for cumulative estimated expense) and CEI, next to the Pivot Table which are populated by the formula: =SUM(C$4:C4)
    The field values for the two existing 'Estimated' columns is changed to 'No Calculation'
    The chart then uses the values of the helper columns as 'Series' and the Year and Month columns from the Pivot Table as the 'Category Axis' labels.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    08-07-2014
    Location
    South Africa
    MS-Off Ver
    2010
    Posts
    24

    Re: unable to get pivot table to display Running Total In correctly across a year end

    Jete,
    Much obliged. Things seem so easy now that the solution is in front of me.
    Thanks also for other (unintended) tip I got from you i.e. simple, but practical naming protocols for headings on top of the helper columns, and also a simple and clean formatted chart.
    What is old hat to experienced hands, are often eye openers for the uninitiated.
    You kindness and effort is truly appreciated.
    I will mark this thread as solved.
    Kind regards.
    B

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,546

    Re: unable to get pivot table to display Running Total In correctly across a year end

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

  5. #5
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Nowy Tomysl, Poland
    MS-Off Ver
    2019, O365
    Posts
    398

    Re: unable to get pivot table to display Running Total In correctly across a year end

    Maybe such a proposal?
    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)

Similar Threads

  1. Replies: 6
    Last Post: 09-29-2015, 08:44 PM
  2. Replies: 4
    Last Post: 08-22-2013, 02:22 PM
  3. Pivot Chart Running Total - Removing Current Year Future Total
    By car3489 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 07-27-2013, 03:35 AM
  4. Pivot table running total
    By Avner in forum Excel General
    Replies: 4
    Last Post: 11-23-2006, 12:08 PM
  5. Pivot table and running total
    By PaulGG in forum Excel General
    Replies: 2
    Last Post: 11-29-2005, 04:20 PM
  6. Pivot table and running total
    By PaulGG in forum Excel General
    Replies: 0
    Last Post: 11-28-2005, 05:45 PM
  7. [SOLVED] % of Running Total to Grand Total in Pivot Table
    By David in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-17-2005, 04:05 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