• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

How do I handle items with no data on running total?|Dashboards & Charts|Excel Forum|My Online Training Hub

You are here: Home / How do I handle items with no data on running total?|Dashboards & Charts|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumDashboards & ChartsHow do I handle items with no data …
sp_PrintTopic sp_TopicIcon
How do I handle items with no data on running total?
Avatar
Richard K
Member
Members
Level 0
Forum Posts: 15
Member Since:
August 4, 2020
sp_UserOfflineSmall Offline
1
August 10, 2020 - 12:05 am
sp_Permalink sp_Print sp_EditHistory

I'm having an issue with getting my charts to display how I would like.

I have two charts positioned above each other as shown in the below image, the idea behind stacking them like this is so that I don't need to show the x axis values on the line chart as they are the same as the column chart underneath.

a1.pngImage Enlarger

 

All was fine, or so i thought. Looking at the above chart it looks fine, but then i realised that there are several months without any returns at the end of 2019 which means that the charts aren't actually in sync. Now when I've had a play around the 'show items with no data' checkbox, I end up with the below.

a2-1.PNGImage Enlarger

 

As independent charts they do display the data as it should be, but they are not in sync with each other as the running total chart displays only up to the last entry in the data while the monthly return shows all months up to the end of the year.

Another issue is that leading months going back to the start of the 'complete' master table are also being displayed. This is not so obvious on the above example, but on the below, these blank months take up the majority of the charts.

a3.pngImage Enlarger

Any suggestions on how I can stop the leading and trailing months from showing while still displaying blank months within the dataset would be really appreciated.

Thanks

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4620
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
August 10, 2020 - 8:58 am
sp_Permalink sp_Print

Hi Richard,

I see you have Slicers so these are obviously PivotCharts. You can use a filter on the Month field to select the specific date range you want, which will allow you to exclude the leading and trailing months. When you apply the Financial Year Slicer filters it shouldn't affect the month filters as these are different fields.

Mynda

Avatar
Richard K
Member
Members
Level 0
Forum Posts: 15
Member Since:
August 4, 2020
sp_UserOfflineSmall Offline
3
August 12, 2020 - 5:20 am
sp_Permalink sp_Print

I've tried filtering the months but this just filters out the same month for each year, im using excel 2013, not sure if that makes much of a difference.

I've done a bit of clipping and pasting in paint to show the desired result in the below image.

b1.pngImage Enlarger

On the left is how the charts displays at the moment and on the right is how I would like the charts to look.

I've shown the date axis on the running total as this makes the chart a bit clearer. As you can see the running total chart runs from 1st April 2018 to 24th March 2020 (this is the full range of the records in the master sheet but the actual dates for this Investment Strategy are from the 14th May 2018 to 24th March 2020). The Monthly Return chart displays all of the months for each year that has data for the specified strategy as I only selected 'show items with no data' on the month field and not the year field (for other strategies that start in 2019, only 2019 and 2020 are shown). The below image shows the fields that I have for the running total & monthly return pivot-tables. The YrMthDay on the running total has the 'show items with no data' option selected.

b2.PNGImage Enlarger

Another issue i've just noticed is that with the 'show items with no data' option selected it almost makes the slicer redundant as it doesn't change the chart view, but instead just hides the columns as shown below.

b3.pngImage Enlarger

Apologies for the long posts

Thank you for the help

sp_PlupAttachments Attachments
  • sp_PlupImage b1.png (192 KB)
  • sp_PlupImage b2.PNG (265 KB)
  • sp_PlupImage b3.png (197 KB)
Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4620
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
August 12, 2020 - 8:52 am
sp_Permalink sp_Print

Hi Richard,

When I said filter out the months you don't need, I should have used the word 'dates'. i.e. make sure your dates in your PivotTable are grouped by days, months and years. Then expand the collapsed fields in the PivotTable for the periods you don't need so that the day dates are displayed > select one of the date cells in the row labels > click on the filter button for the row labels > deselect the dates you don't want in your chart.

Hope that points you in the right direction. If you're stuck, please share your file so we can show you how to do it.

Mynda

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Sameh Alami
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
Hans Hallebeek: 188
Newest Members:
JUDY MLL
Scot Bailey
Kate Dyka
Kwaje Alfred Mogga
thong nguyen
Appiagyei Kofi Frimpong
Hilary Burchfield
Richie Wright
Adel Kock
Barbara Murray
Forum Stats:
Groups: 3
Forums: 24
Topics: 6548
Posts: 28672

 

Member Stats:
Guest Posters: 49
Members: 32834
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.