• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with pivot chart losing format

Greenbriars

Member
Hi

I have been trying to teach my daughter some more advanced Excel techniques than she learned in school and she suggested using some of her exercise data to provide a real life example.

Her thinking was a dynamic chart , but I thought that we could be more creative, which is where I perhaps fell down. Without understanding all the consequences I chose to create a dashboard with my daughter's required chart, but with other data and crucially a timeline to select the time period analysed.

I enter the daily data in the "Data" tab, which also contains a cell for the daily target. The "Chart" tab contains a pivot table based on the data in the "Data" tab together with various calculations to generate the data for the dashboard. The pivot table is updated automatically when the data changes via a macro. I've had to use a workaround by creating a range called "Steps" which will allow 1,500 data points because I don't think there is a way to treat a pivot table column in the same way as a table.

The main chart is a combination chart with the actual data displayed in bars and a target and period average displayed as lines. This works fine, but if I select a time period which contains no data and then reselect a period with data, the chart type changes to a bar chart.

Question 1: Is there any way I can fix the format (type) of the chart even if the timeline has called for no data?
Question 2: Can I analyse a pivot table in the same way as a table? i.e. referring to the pivot table and a column in a formula.

I attach a copy of the spreadsheet.
 

Attachments

Greenbriars

Member
I have left this problem for a couple of weeks and have now returned to it. I have managed a workaround which might be of potential interest to others.

1. I am now fairly certain that it is not possible to restrict the scope of the timeline.
2. I can use dummy data to add an insignificant number for the first day of every month covered by the scope of the timeline.
3. I have changed the TARGET field to report only the MAX values to avoid double counting of data for the first of each month.
4. I have used a calculated pivot table field (using AVERAGEIF) to correct the average steps to exclude the insignificant numbers from the average calculation.

It is JUST possible that this may be of some interest or use to others.
 

Greenbriars

Member
I just wish that Microsoft would let users restrict the scope of the timeline. Unless I am missing something the upper limit appears to be December of the last year for which there is data, but the lower limit is 1 January 1900. That's one heck of a lot of redundant dummy data to have to use to fix the problem.

If anyone believes that my understanding is incorrect then PLEASE let me know.
 

Greenbriars

Member
Does anyone know if there is a process to report "must haves" or "nice to have" to these lovely people at Microsoft? I guess that it is the MVPs who will have most influence.
 

Greenbriars

Member
Thanks for the reply vietm.

Perhaps I don't understand what you are trying to tell me.

1. There is a table populated with data.
2. The pivot table is updated when you add data, therefore the pivot table will always contain data.
3. The pivot chart is based on the pivot table, but selected (filtered) using the timeline.
4. The timeline allows you to select periods which contain no data. IE, periods in the current year up to December 31 and periods prior to any valid data back to January 1, 1900.

Selecting periods with no valid data simply resets the chart type to a bar chart and the colours revert to the Excel defaults.

For the moment I have given up on the pivot chart idea and am using a conventional chart, but I'd love to find a way to filter the data to show only specific time periods.
 

vletm

Excel Ninja
Greenbriars
Isn't one of Your challenge with chart - that after You refresh Data-sheet Your Chart will change its format?
- if no refresh if no data!
... but I tested and noticed and ... why?
Maybe something as Your the last line ...
Why do You have data (Table1) (which can as well as filter as needed) and
You used pivot-table based that data (Table1)
instead of using straight Your data (Table1)?

'Your original way' You would have like two times data ... instead once ...hmm?
... I didn't test ... my previous hint ... to check is there (Pivot-table) any data before Refresh Your chart.
> Try to take one 'like extra step away' <
or
You would write code which refreshes Your specific settings to Your chart.
 

Greenbriars

Member
Thanks SO much for taking the time to respond to my question vietm.

I'm not sure that I understand your reply, but I'll try to follow what you have said.

The main point of the project was to increase my daughter's understanding of Excel and I wanted to show her the concept of an interactive dashboard. The interactivity would be achieved using a timeline. What I didn't appreciate before commencing was that there was no error checking in the Excel timeline and this is that has caused my problems.

You have asked this question:

Why do You have data (Table1) (which can as well as filter as needed) and
You used pivot-table based that data (Table1)
instead of using straight Your data (Table1)?

The simple answer is that I wanted to create a pivot chart and use a timeline to filter the data. This was not something that my daughter had learned at school. I thought it would make the dashboard more functional. I realise that this means that I will always have all my data held once and that a sub-set of that data (could be 100%) will be transferred to the pivot table. I would prefer that the data was not recorded twice, but based on the volume of data I took the view that it was not a problem. There would not be any problem if I could create a timeline which operated on the data contained in Table1.

I may be wrong, but what I believe I have done is the following:

1. I enter data into Table1
2. A macro (VBA script) automatically updates the pivot table. This will be based on whatever the timeline settings are.
3. The pivot chart displays the data from the pivot table based based on the timeline settings.

I believe that there could be 3 solutions to this problem:

1. Restrict the timeline to only select periods containing valid data
2. Prevent the pivot chart from updating unless it was charting valid data
3. Create a filter with similar functionality to the timeline to operate on Table1 and to be included in the dashboard in place of the timeline

If I had realised the restrictions / limitations of the timeline I wouldn't have gone down the pivot chart route!

Many thanks for your interest and help.

Alan
 
Top