Smartsheet Dashboard - Chart Colors Changing

Bolton

Hi Everyone,

This is actually my second post and 3rd issue I have had on this topic. My chart colors won't stop changing when values go to 0 then back to a positive number again!

My 1st solution attempt I tried adding +0.0001 to the end of each formula, but this kept multiple 0's on the display which made part of my chart unreadable.

My next attempt was making all of my values go above 0, assigning a color, and then resetting it back to its actual number. This unfortunately did not work either and I attached some pictures below to show this.

Picture 1: This is how all of my colors originally showed after populating values

Picture 2: This is what it should look like and what I assigned them after picture 1.

Picture 3: This "3" here should be orange but it auto defaulted to blue.

Any help would be greatly appreciated it, I am at a total loss on what to do at the moment.

Thank you.

Brian Wilson DC

@Bolton can I ask you what the individual numbers represent? And are there at total of 5 possible categories that your data could fall into? I have gotten this to work for another project, it would just be easier to know what data you are using and I can replicate it step-by-step for you.

Bolton

@Brian Wilson DC Of course! These values are the sum of different status values for equipment. There are indeed 5 possible categories or statuses. Green here correlates to a "GOOD" status and red would correlate to a "DUE" status. The actual status for this particular area is GOOD=6 and the other 4 statuses are all 0. I set all of this up and it worked perfectly until recently a piece of equipment would change status and go from 0 →1 and the color would be different.

Snip 1 below shows the summation of the values and the 5 statuses. (I am using aforementioned +0.0001 here on the orange category to keep the colors from messing up)

Snip 2 below shows why I can't use this fix for my smaller area charts and has the 0.0001 added

Snip 3 shows what a properly functioning area chart looks like without .00001 added

Brian Wilson DC

@Bolton In the interest of providing you an answer that will hopefully work, even without knowing what data you are trying to put into that chart, I offer the following (Using my Kanban Example):

I have four different Kanban stages I can assign to a task depending on what stage a Control Panel is at on my projects. These are White, Pink, Yellow and Green. You will see in this first example, I have only selected white and pink as options and when I use my dashboard. Here is the data selection for the data I want to use, which I have used a COUNTIF formula to summarize how many Kanban Phases of each type I have. The blue highlighted is what I put into my chart:

You will see it defaults to these two colors. I am going to change the two blue shades to a white and Pink to match my data:

Now I am going to artificially change my data so I can report a "Yellow Kanban" and a "Green Kanban". So I am going to temporarily take two of my controls panels and have them reflect Green and Yellow states:

You will see that it remembered my White and Pink Settings and now it has arbitrarily assigned two new colors to what I want to be yellow and green.

So I am going to set those colors to the colors that I want, which will make the Chart Widget remember that setting as below:

Now I can change and manipulate my data any way that I'd like to and as long as I'm using one of those same four values (ideally you should be using a drop down menu to insure spelling and punctuation remain consistent) it will automatically apply those settings:

I change my data to all Yellow and Green like so:

And the Widget changes to this:

If I revert the data to another breakdown like so:

It will automatically revert to the appropriate colors:

Again, the most important thing to note is that the category you are assigning the color in the legend must be SPELLED and PUNCTUATED the same. So using a dropdown list for the selection of your legend options is the ideal setup for this. Otherwise, it treats every capitalization or spelling change, or spacing change as a separate and distinct category and will assign a new color to it.

I hope this helps!

Regards,

Brian

Brian Wilson DC

@Bolton As I was typing my answer you posted your response. Let me know if what I suggested worked for you. Instead of making a fake entry with .001 as your value just arbitrarily change one of your existing entries to the status that isn't reflecting by any of your existing data points. So I replicated your data into a chart and then arbitrarily changed 1 of the "good" statuses to "Ext-Looming". I also changed it to a semi-circle and to show value labels of "Amounts" instead of "percentages"

Once you save this you can then go edit your data however you'd like. If I simply remove the one "Ext-Looming" that I fudged to make the legend colors, then my semi-circle reverts to this:

if I completely remove a few categories it looks like this:

I hope that helps!

Bolton

@Brian Wilson DC Brian, I really appreciate you taking the time to help with this! I am starting to wonder if part of my problem is coming from using the "GOV" platform as my layout in settings doesn't look like yours

I replicated how this solution fails for me below in snips.

Snip1: This is the correct data as of today.

Snip 2: I change the values to populate the 0's (its already duplicating colors as you can see)

Snip 3: I set the colors in the dashboard as they should be

Snip 4: I erase the fake data values so this is true again

Snip 5: I add 2 new "fake" values and the colors do not hold (ironically enough they both became the exact same color)

Please let me know if you think I am missing something. I don't have the same visual layout shown below that you put in your description

Mine looks like this

Brian Wilson DC

@Bolton It would "appear" that you are replicating, to the best of your ability, the same steps I used to get this to work. I would reach out to their IT maybe and use this thread as a reference to show the differences between the two platforms?

I wish I could be of more help, but I don't have access/experience with the GOV platform.

RWinslowGov

I use the 0.0001 method of saving the colors. When I had a prodesk session with Smartsheet they said that was the only way. I work on the GOV platform. We were required to change all of our graphs to bar graphs to satisfy the new ADA compliant rules. The bar graphs look fine with 0 values but they do show but with no color.

Bolton

@Brian Wilson DC thank you for the help!! if I ever come across a solution I will try to come back to here to relay

Bolton

@RWinslowGov understood! thank you for this info. I am exploring work arounds but stinks our chart formatting can't be the same as the non-Gov version…