Text Values showing as zeros in Scatterplot

I have a sparse data set that I am trying to plot on a dashboard, and we were using a line graph, but because the data is so scattered it doesn't present well:

If I hover over the graph you can see the missing data points:

So I thought I would try a scatter plot, but when I do all the "zeros" show up:

These data are graphing from an IFERROR(AVG(COLLECT())) formula and the "if error" returns "no data" - I am wondering how to keep those values from plotting as zeros.

Find more posts tagged with

Dashboards

charts

Accepted answers

Jgorsich

I think your base problem might be your thinking about what you are trying to represent in the graph. If you solved your scatterplot problem it is just going to look like your line chart without the connecting lines - which you state doesn't present well. Though, if you DO want this, graph another column that has all 0s as the values, make it the first series graphed and set the display color to white so that it overwrites all the others- that might work. You can see right now your orange series overwrites yellow and blue at the 0 line, just make an additional white one and you won't see the marks. PERSONALLY, if you are going to do this, I'd duplicate the entire dataset in a helper sheet and rather than "" for the empty cells I'd use something like 80% of the minimum value of the real data so that the Y axis doesn't go all the way to 0 for no reason. If you do that, obviously your white series needs to be that same value.

If you are trying to show the trend over time of your different datasets, the line chart with missing values interpolated may make the most sense - in which case, rather than having no data when there isn't a value you'd want the data from the cell above (or a smooth transition to the next point depending on the nature of your data). Any kind of interpolation is going to require the ability to reference other cells and rows which (if you want to use column formulas) is likely going to require a helper column called "row" as described in the accepted answer to this thread: Is there a way to reference the row above? - Smartsheet Community

If the times when the value drops to zero is relevant then the scatter plot and the line plot are both showing what you need them too by making the zeros visible.

If you are trying to show some other relationship you may want to consider manipulating your data in a helper sheet prior to graphing entirely (I do this OFTEN, I very rarely want to graph my data in the format I want to capture it in; any dashboard of mine usually has at least 2 helper sheets and a couple of reports that are kept hidden from the users).

Historically, when helping people with data visualization issues, my advice has been to use pencil and paper to draw the ideal graph you want and at least 2 or 3 alternates that would also work (just to make sure you actually like your first pass "ideal"). THEN figure how your data would need to be manipulated to allow that to be generated by the computer. ONLY THEN start messing with the graph options of whatever program you are using.

In your case - what do you WANT displayed when one term is blank but the others aren't? Should the line continue through level? Slope to the next point (and what do you want if there ISN'T a next point)? Disappear? Something else?

All comments

Paul Newcome

Have the IFERROR output a blank instead of "no data".

=IFERROR(AVG(COLLECT(………..)), "")

jjg279

@Paul Newcome No love I don't think. Still plotting as zeros:

Jgorsich

If the times when the value drops to zero is relevant then the scatter plot and the line plot are both showing what you need them too by making the zeros visible.

jjg279

Hey @Jgorsich , this is so helpful! Thank you for your response, this was very detailed. We are trying to graph the weights of some captive birds over time, the graph represents an three-year average broken down by week, so some weeks (a lot of weeks for this bird) are missing.

We would like the scatter plot in this case because you can actually see all the points that are actually there; and get a rough trend, but we can't/shouldn't extrapolate our missing spots because it could have ramifications for the bird health. I just couldn't think of a way to "vanish" the zeros - because they aren't zeros, they are missing, and they are throwing off the scale.

Jgorsich

So, unfortunately, in my experience there is no good way to go with disparate X values with 2 series in a scatter plot in Smartsheet (vs Excel, where you can easily put 2 series on the same graph and given them different sets of data for X and Y each); you have to share a single X value column, which will force empty values into your graph.

So, I'd think you have 1 basic solution path, but with an easy and complicated version dependent on whether or not you want trendlines to see if a bird is loosing or gaining weight overall.

Basic path (easy):

I'd do what I recommended originally - have your data pulled into a helper sheet where you populate any empty values with 80% of the minimum value tracked in your total set of weights (unless the weights are close enough to 0 to make it not annoying if your Y column goes to 0). The data in the helper sheet should be arranged such that the first column is your X values (week number or date, presumably) and the VERY NEXT column is solely populated with your "no value" value (either 0 or 80% of your minimum value). This series you'll set to be the same background color as your dashboard as a whole so that it looks like there is simply nothing there. This series will be shown on top when there are overlaps, so that any values that fall to your "no value" value seem to just disappear.

Complicated path (trickier):

If you want trend lines for the data (which I don't really recommend on a combined chart but is possibly useful on a "per bird" chart), you have to add calculations to create a new series that assigns the value for what the trend line is for each X value and then also graph that on the chart. Moving averages are relatively straight forward - just use some helper columns to extract only cells with data and then decide how many of them you want to average and fill the formula down (if you want to use column formulas this is also possible but slightly trickier). Linear trend lines CAN be done without iterative functions that get impossible in Smartsheet, but they require some calculation to be done before they can be generated - here is a thread on the formula:

statistics - What is the equation used to calculate a linear trendline? - Mathematics Stack Exchange

I've not tried to add formulas for polynomial or other more advanced trend lines to Smartsheet so can't speak to whether or not they are easy to add in. Doing a linear requires 3 or 4 extra helper columns to pull off already and I've not felt the need to go more complex without just deciding to switch to something like R or Python or Excel instead. Unfortunately there is no built in function for trendlines that I've found native to Smartsheet.

Given that I'm a fan of birding, and a fan of Cornell's bird efforts in particular (and I've got Merlin Bird ID installed on my phone), if you'd like a more direct hand in setting this up you can message me directly and we can try to do a screenshare sometime with your actual sheets. I'd be happy to free up some time to help! (not a pitch for consulting services, I'm just happy to help the group that has made it a HECK of a lot easier for me to identify a handful of birds!) Edited - just realized there isn't a way to individually message someone. jgorsich at gmail is my personal email (spelled out to try to avoid web crawlers).

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.