Hey,
currently I struggle a little bit with a planned chart and I hope you can help me - if it's possible at all. 
Attached is my workbook and on the "chart" tab you can see the Pivot Chart I created based on the Pivot Table right beside it. It shows prices per day over several months and I need that daily detail level within the Pivot Table because else it would show me like a monthly average price which is not what I want to show. However as you can see in the Pivot Chart the axis label shows the months and years which is fine but some dates as well.
And here comes the tricky part: Is there any way to show within the charts only the months and years without the dates?
I tried to set the dates in the Pivot Table to "#NV" so they won't get shown but Excel doesn't allow this. Within the formatting options I wasn't able to find any option to hide the dates either. Maybe it won't be possible to hide em at all, but I thought eventually you would have an idea... 
Cheers
Tobias
Hi Tobias,
Not in a PivotChart. You'd have to create a manual chart from the PivotTable data as described here:
https://www.myonlinetraininghub.com/creating-excel-charts-from-multiple-pivottables
You have 3 options for creating the manual chart table of data:
1. Copy and paste the PivotTable as values and then link the chart to that data, ignoring the column containing the actual days/date.
2. Create a manual chart tabel with formulas which reference the PivotTable and then link the chart to that table, ignoring the column containing the actual days/date.
3. Create dynamic named ranges that reference the PivotTable and use these in your chart (one range for the horizontal axis labels for Year and Month, and one for the data). Dynamic named ranges are covered in sessions 5.12, 5.13 and 5.14 of the Dashboard course.
Let me know if you get stuck.
Mynda
Hi Mynda,
honestly this time I'm totally stuck though having your three options. 
The first option doesn't seem to be an option for me as there is nothing dynamic to it, meaning I would need another workaround to update data so it doesn't sound like a usefull solution to me.
For the second option I have a problem dealing with the actual time period. If it's just one month there is no problem, but when having two or more months selected I always have like an empty row which just contains the year or the name of the new month (as shown in the Pivot Table) and currently I have no idea how to avoid that.
On the third option I would have to get my data into a horizontal way instead of the vertical one I have now and here I really worry about the limitation of usable columns. Or I'm just a blockhead and don't get the right view on transferring the sessions you mentioned to my problem.
So I would really appreciate if you could give me some practical support!
Cheers
Tobias
Hi Tobias,
There is an element of blockhead-itis 😉 or maybe you haven't practiced the dashboard course sessions and are trying to go from my example straight to your data.
The first option above is great if your data isn't going to change or be updated/added to. It wasn't clear whether this was the case for you from your original question.
In regards to your 'empty row' you like to have. I don't know where this empty row is that you're referring to.
For the 3rd option; remember both INDEX & OFFSET can return a vertical range, horizontal range or a multi-column and multi-row range, so it doesn't matter which way your data is laid out, you just change the dynamic part of the formula to count vertically instead of horizontally and vice versa.
See sheet chart (2) in your file attached. Note: I've changed the layout of your PivotTable to 'classic' so that the row labels aren't nested. This is necessary for nested axis labels in a regular chart.
Mynda
Hi Mynda,
thanks for your feedback now things became much clearer! 
The "empty rows" actually resulted because I created a manual chart table and referenced the Pivot Table. But as my view wasn't set to "classic", so were in the nested setup, this "empty row" came from the rows where either the months "Oct", "Nov", "Dec" etc. or the years "2015", "2016" were shown.
While now having the big and great hint on the "classic" layout there is no "empty row" anymore and I can easily work with your second option and I will even give the third option another try. Sometimes you simply don't see the solution even if it's right ahead and pretty obvious...
So again thank you very much for this great advise and hint!
Cheers
Tobias
You're welcome.
BTW that 'hint' is also covered in session 4.01 where I show you how to structure your data to nest axis labels. You might like to go through the course again at some stage because there is a lot to take in and much of it is easily forgotten if you don't use it right away.
Mynda
