Dashboard Error - Data Integrity

Patrick Dugan

Ok....so, I go through the work of complex formulas and get to a place where I can count the number of Green, Yellow, Red, and Completed projects. When I attempt to either link that info to another sheet or use it in a chart for a dashboard, the data comes out differently. You can see here for instance, I should have one project in the "Red" but it isn't in the chart at all. Thoughts?

Paul Newcome

Have you double checked that you are referencing the correct data in your chart?

Patrick Dugan

@Paul Newcome Yes sir!

Lucas Rayala

@Patrick Dugan -- looking at the actual names in your pie chart legend, you have "Green", "Yellow", and "Blue" (but not "Red"). These written descriptions don't correspond the the colors assigned in the chart. I would sync that to avoid confusion. I'd also note that the percentages look correct if you are comparing them to the written descriptions.

Patrick Dugan

@Lucas Rayala The issue you highlight is not one of my making. That is the exact problem. The selected cells for inclusion in the graph include all 4 colors....they'd have to based upon how SS works. I couldn't exclude a middle row from the selection and have it function at all. I appreciate the note but fear your observation is tied to a root cause in how the data is being from the source sheet, not in formatting.

Genevieve P.

Hi @Patrick Dugan

You note that you are using a formula to generate the numbers. Values of 0 or text values will be automatically skipped in a Pie Chart and will not display at all, or even be listed in the legend.

Is it possible that your Red value is seen as text instead of numerical? Can you post the formula you're using?

Patrick Dugan

That is an interesting question. So, using a COUNTIF function to count red, yellow, and green. Same formula throughout, which has now been doublechecked by the Smartsheet folks. It would be difficult to believe that it would count the green fine but wouldn't count the red. As an added note, I went in and copied the entire sheet and replaced all formulas. It worked for about 4 hours and then reverted to the screenshot I shared. In that successful 4 hour window, it counted accurately in graphics, linked sheets, and the source sheet.

Patrick Dugan

Genevieve P.

Hi @Patrick Dugan

Would you be able to post the COUNTIF formula you're using, and show us the source sheet data (but block out anything sensitive)?

Is there a possibility that the COUNTIF is sometimes returning 0 for that value? To test this, I would add a bar chart on your Dashboard next to your pie chart. Bar charts will show 0 values so using this extra chart as a troubleshooting tool, we'll be able to see if the chart is viewing the value as 0 or as text.

Thanks!

Genevieve

Patrick Dugan

=COUNTIF({Copy of Simplify Project Sheet Range 1}, "Green")

It is returning a zero but shouldn't. I'd changed it to a bar chart already as part of updates to the layout. The question is, why won't it count the all the colored bubbled equally? So the sum of all colored bubbles is accurate, 32 today. However the green counts 6 more than are present, the yellow 5 less, and the red count none.

I'm not sure if that helps the discussion along... :D

Genevieve P.

Hey @Patrick Dugan

Thank you, this does help a lot! 🙂

It narrows down that the issue is with the formula and the values it's surfacing, versus with the chart.

Are you able to post a screen capture of the source data and circle/point to the column you're referencing in this:

{Copy of Simplify Project Sheet Range 1}

Patrick Dugan

I'm interested to see where you're going. This column is the reference for the count. This columns created through the following rule comparing variance, risk, and status to resolve an overarching color status: =IF(AND([Project Status]@row <> "Completed", [Project Status]@row <> "Not Started", [Project Status]@row <> "Removed", OR([Translate Variance]@row = "No", [Translate Variance]@row = "Ahead"), OR([Project Risk]@row = "None", [Project Risk]@row = "Low")), "Green", IF(AND([Project Status]@row <> "Completed", [Project Status]@row <> "Not Started", [Project Status]@row <> "Removed", OR([Translate Variance]@row = "30", [Project Risk]@row = "Med")), "Yellow", IF(AND([Project Status]@row <> "Completed", [Project Status]@row <> "Not Started", [Project Status]@row <> "Removed", [Translate Variance]@row = "90", [Project Risk]@row <> "None"), "Red", "Gray")))