"The source data for this chart has changed" - Charts with Zero Values

Mr.Hyde

Hello.

I have noticed that charts within a dashboard do not display and throw up a message: "The source data for this chart has changed" when they are pointed at sheets or reports where the value is zero / 0.

This is problematic for me since I have created a dashboard for a template set of sheets and reports, and the default value for the reported element is 0, and may not immediately (or ever) change to a value higher than 0 over the lifetime of the project the template set and dash are intended.

Since the dashboard is customer facing the appearance as a whole is very poor to what is otherwise a dashboard with a great deal of effort and functionality put into it...  The message itself (attached) is also a warning error with the yellow triangle, and the wording doesn't go the reasonable distance to explain the situation... it's just a techie error which is unwelcome on the front end.

Is there no way to mitigate this situation by being able to choose your own message such as 'nothing to report', or '0' or even a blank chart?

Any help would be most welcome, especially since this is a client feature I am trying to deliver.

Regards

Steve. 

 

Paul Newcome

Pie Charts won't show a 0 value, but all of the other charts will. I have run into this issue before as well and found that displaying things in something other than a pie chart is the way I usually go. I will allow the first value to be a 0 and then everything else a blank.

Mr.Hyde

Thank you for your reply Paul, I admit I did not realise that was the case for all other chart types.

This is still not ideal since pie is the way the client wanted the data represented, but they like the look of a blank bar better than they do the error, so in the short term I have taken your steer and used another bar. 

Long term let's hope there is some kind of work around to this error reporting on dashboards.

 

Thanks,

Paul Newcome

Of course the client wants a Pie Chart. They always want the one thing they can complain about. Haha.

 

I'm glad I could help with at least a short term solution, and hopefully you are able to find a better long term solution.

karla.sosa86386

Hi Paul

I have the same issue and I really want to keep pie chart style, this how I played with the data to keep my pie:

I'm measuring actions taken when a defect is found in the last week, I have multiple options such as re-work, scrap, ship as is, etc..

If there are no issues found, then all action taken values go to 0.

My solution was to add another option called "No actions needed" and assigned it a value of 1 when all the other results are 0: =IF(SUM(I30:I34) = 0, 1, 0)

This way, there will either be a % of one or more actions taken, or 100% no actions needed.

Hope a similar approach can help on your case

 

Paul Newcome

I actually really like this idea! Good thinking! 

Mr.Hyde

good work around Karla, thanks for posting.

karla.sosa86386

thanks guys, glad to give back one idea after all the good tips I'd got from this forum

 

Andrée Starå

Hi Karla,

Great workaround! Nicely done!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting