Chart Colors

Ramsay Zaki

Am I doing something wrong in my setup of Pie Charts?

Pie chart colors should stick to their originally set up value even if there is no data to show. For example, my dataset has 5 columns selected. Let's presume for a moment that each column initially has the following values: 5, 8, 3, 9, 2. When creating the initial pie chart, I am able to select a color for each value set... let's say Red, Blue, Green, Yellow, Grey (in that order). Now, let's say a few days later column 3 has a value of 0... so the dataset now looks like 5, 8, 0, 9, 2. I would expect the pie chart to show the following colors: Red, Blue, Yellow, Grey. Instead, it shows up as Red, Blue, Green, Yellow. This is not an appropriate way for charts to work.

Ramsay Zaki

It has been a couple of weeks since I wrote this original post and I can now confirm that the method I state above works great. When you set up the chart (Pie, Bar, etc), you need to ensure that ALL data elements have data in the underlying table. This means you might need to put fake data in the underlying data cells for the purpose of setting up the Chart. Once you assign the colors and save the chart, do not open the widget for editing again. If you just leave the widget alone, the colors appear correctly in perpetuity.

If you do need to edit the widget, you need to go back to the underlying table and make sure all cells have data in it again before you edit.

Debbie Sawyer

Sadly I have witnessed this behaviour too. I don't know if there is a way of setting it so that they don't change when a series becomes null. (i.e. not fed into the chart).

Debbie Sawyer

I have been discussing this with a colleague and he uses a method (workaround) to ensure that the series don't leave the chart data and then render all other series colours changable!

He said if you place:

=VALUE( YOUR ORIGINAL CELL VALUE/FUNCTION HERE ) + 0.00001 around the current functions in the cells that are feeding the chart then your zero values will still show on the chart as 0 and the colour will stay assigned even if the series returns 0.

eg

If your chart is being fed like this:

Column 1:

=SUM(Column2:Column2)

If this formula returns 0 value (as nothing is in column 2 yet) but you still want this cell to feed a chart you'd change it to:

=VALUE(SUM(Column2:Column2))+0.00001

Then the value will still be 0 but it will show in the chart.

I hope this makes sense. I haven't tested it myself - just passing on a colleagues wise words 😀

Hope it helps.

Debbie

Doug Harrison

This problem is killing me. The irony might be I am blind to the work-around, or the fix. Can anyone help here? I would be so grateful. This seems so basic -- remembering a color in a pie chart! It's SO basic, I'm beginning to think the problem is me. My application is exactly what "Ramsay Zaki" describes in the start of this thread. I look like I don't know what I am doing to the groups I support. That makes me feel like I need to look for a different software platform.

Ramsay Zaki

So, I found the issue. It actually does work like we need it to.

When initially setting up the pie chart, make sure that ALL potential data values have a # >0 (even if it is "dummy data" for the purposes of setting up the chart). Then, assign the color you want to each data value. Then save the chart and save the dashboard. Now, you can update your data on the sheet however you want. The pie chart will now follow the correct colors. BUT... beware... if you open the settings for that pie chart again and the underlying data doesn't have a value for each element, the colors will mess up again. so, basically it works for the initial setup but if you need to change anything on the chart, you need to first go back and make sure all data values are before changing the chart setup.

It's quirky but does work.

Paul Newcome

@Doug Harrison Have you read through @Debbie Sawyer's solution? I have not tested it, but it seems like it should work.

Silvia Rangel

@Paul Newcome I tested it and it's showing the .00001 at the end

Ramsay Zaki

@Silvia Rangel I tried that too and didn't like that it shows the 000001. Take a look at my last comment about. The colors do work properly but you need to be VERY careful during the initial setup of the pie chart.

Silvia Rangel

@Ramsay Zaki Thanks, your option was my next step. Question, my dashboard has been up and running for a little while now, do you recommend to trash it and start from scratch???

Ramsay Zaki

@Silvia Rangel No, you dont need to do that. Just make sure the data in your underlying sheets all have a value Then go and edit the pie chart widget. Then save the widget. Save the dashboard. Them put the data in your underlying sheets back to what you need to be.

Paul Newcome

What if you adjusted the sheet so that it doesn't show the decimals? It will still store the decimal value, but it won't be visible. It may adjust what is shown on the chart.

Doug Harrison

I am now backing @Debbie Sawyer's solution into my existing dashboards. I changed the "show decimal" property value for each cell to NO DECIMALs in the calculation sheet; that remedies the odd decimals on the dashboard. I do hope this works. I have many =SUMS that current have a zero value. They still show up as blue, or purple or whatever. Possibly I can add some temproary dummy data to get them to positive, and then go in, adjust and save the widget.

Paul Newcome

Good to know that adjusting the shown decimals on the sheet also adjusts them in the chart. I hadn't tested any of it, so I wasn't 100% positive it would work.

I may have to dive into a few of my metrics sheets and dashboards as well now. I have quite a few that could definitely benefit from being able to "lock in" colors like this.

Paul Newcome

@Debbie Sawyer Whoever your coworker is... Give them some kudos for me please. Haha

Silvia Rangel

@Paul Newcome I will definitely give it a try!

Debbie Sawyer

Glad this worked for you.

If you set the decimal to 0.00001 then you can show up to 4 decimal places in the data which is plenty for anyone!

James Harris is my colleague - he works with me at Smarter Business Processes just hasn't found this community yet! Far to busy designing API solutions for our clients :) Ha ha

I'll pass on all your thanks to him! ;)