+ Reply to Thread
Results 1 to 12 of 12

PivotChart - Hide Data Points Beyond Current Month

  1. #1
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    PivotChart - Hide Data Points Beyond Current Month

    Hi all,

    I have an Excel 2010 pivot chart that is taking external data from Access 2010, summing a volume, and displaying it by month. I have grouped the data beyond the dataset so that I can show the whole year, even though there are no records with sales in Sep-Dec.

    My question is whether it is possible to hide the datapoints after the previous month, e.g. we are in Sep so everything from Sep-Dec should not appear as a datapoint. Currently the pivot chart sums these months to 0, and from Aug to Sep there is incorrectly what looks like a sharp drop in sales to 0.

    The complication is that I can also have sums of 0 in previous months, so I cannot identify the datapoints I want to remove by checking if they are 0 or blanks. I need some way of checking if the displayed month is greater than or equal to the current month. Unfortunately you cannot use a variable function (such as NOW) in a calculated pivot field.

    Does anyone know (or want to try) a way to get around this? See below image for clarification.

    pivot_hide.PNG


    Cheers!
    Design everything to be as simple as possible, but no simpler.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: PivotChart - Hide Data Points Beyond Current Month

    The easiest way to solve this is on the source data file. Create a column that checks to see if the date is less than or equal to today. Then use this as a filter on your pivot table
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: PivotChart - Hide Data Points Beyond Current Month

    Hi dflak,

    Unfortunately that is not possible since the pivot table is summing sales records in the database and there are never any sales records that are dated in the future; therefore past/future flags would not be applicable. As per original post, I have just extended the axis beyond the dataset and the pivot table (correctly) sums future months to 0 since there are no records.

  4. #4
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,957

    Re: PivotChart - Hide Data Points Beyond Current Month

    Use the formula = IF(Volume =0,NA(),Volume) in the Calculated Field.

  5. #5
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: PivotChart - Hide Data Points Beyond Current Month

    Hi josephteh,

    As per my original post:
    Quote Originally Posted by Stormin' View Post
    The complication is that I can also have sums of 0 in previous months, so I cannot identify the datapoints I want to remove by checking if they are 0 or blanks.
    Any other suggestions?

  6. #6
    Forum Expert teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: PivotChart - Hide Data Points Beyond Current Month

    Use a regular chart instead of a Pivot Chart. Build a helper table in necessary, that pulls the data from the pivot table. Use formulas that set the cell values for the future months to NA(), then the data points will not be plotted.

  7. #7
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: PivotChart - Hide Data Points Beyond Current Month

    Hi teylyn,

    A viable solution, thank you, however the user selects one or more specific customer(s) using the report filter on the pivot chart and I wish to preserve this functionality.

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: PivotChart - Hide Data Points Beyond Current Month

    You may need to post as example workbook to illustrate the problem as my one does not display data that is not in the source data.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  9. #9
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: PivotChart - Hide Data Points Beyond Current Month

    The attached is more akin to how my file is set up. The database I'm using as the source has one record per item sold.
    Attached Files Attached Files

  10. #10
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: PivotChart - Hide Data Points Beyond Current Month

    Hi,

    I think you would require some code to hide the relevant items- perhaps
    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: PivotChart - Hide Data Points Beyond Current Month

    You would need to set the end date of the grouping given your source data.

  12. #12
    Valued Forum Contributor
    Join Date
    03-16-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    371

    Re: PivotChart - Hide Data Points Beyond Current Month

    I was hoping to avoid VBA if possible. My requirement seemed 'typical' (extending an axis into the future without extending the points) but alas, it was not to be so.

    I used nitwit's code as a starting point and wrote the following today, which works as intended.

    My charts are in their own worksheets, so this goes behind the chart sheet:
    Please Login or Register  to view this content.
    and this goes into a module:
    Please Login or Register  to view this content.
    Hopefully this will assist anyone looking to achieve the same functionality.
    Last edited by Stormin'; 09-25-2017 at 11:16 AM. Reason: ActiveChart -> Me

+ 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: 1
    Last Post: 04-29-2016, 01:28 PM
  2. [SOLVED] Set PivotTable Filter to Current Day, Current Week, Current Month, or Current Year
    By EnigmaMatter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2014, 08:31 AM
  3. Hide data without removal in PivotChart?
    By alexpsyched in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 06-14-2014, 12:10 AM
  4. [SOLVED] Pick a cell containing current month actulas based what the current month is.
    By vanbasten007 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-10-2014, 01:17 AM
  5. [SOLVED] PivotChart Query: How to summarise data by month on the x axis
    By The_Snook in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 07-23-2013, 08:33 AM
  6. [SOLVED] Auto populate cells from data in a 6 month range starting with the current month
    By ecarnley349 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-21-2012, 07:32 PM
  7. Hide the current month columns
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-28-2006, 07:10 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