Line Charts in Dashboards - Need two lines on one chart

dhall

Hi there! I'm building a dashboard from data I'm collecting from and individual and their respective manager.

The individual rates themselves on a metric each week. The manager rates the individual on the same metric each week. I convert the ratings to numbers and put them in a row their own row - one for the individual and one for the manager. Each column is a week with a number to correlate when they performed the rating.

I want to see how the Manager rates the individual compared to how the individual rates themselves on the same chart as it's the same metric. I figured line chart would be the best so we can see the up and down week to week.

However, when I put these rows into a report for a dashboard, it only charts out the top row.

Am I missing something on how to turn it into two lines on a chart?

Genevieve P.

Hi @dhall

Can you post the formula you're using for this conversion?

It sounds like perhaps these numbers are being seen as Text instead of Numerical. This can happen if a formula returns the number with quotes around it, like so:

=IF(Data@row = "Four", "4")

You'll want to ensure any numerical outputs don't have quotes, like so:

=IF(Data@row = "Four", 4)

Let me know if this may be what's causing the error!

Cheers,

Genevieve

Genevieve P.

Hi @dhall

If I'm understanding you correctly, you have a source sheet set-up similar to this:

And you want both rows to appear as two lines in a Chart Widget. If so, then yes! This should be possible. In my test Chart I made sure to map both rows as the Source:

Note that both full rows are selected in blue. Then I have my Chart Widget settings as follows:

If you're still unable to map both rows, it would be helpful to see screen captures with Test Data so we can understand set-up and help further!

Cheers,

Genevieve

dhall

Hi @Genevieve P. ,

Thanks for the response! So it turns out for some reason one of my rows "cannot be charted" which makes no sense. Here are some screenshots for reference:

I've selected these two rows - Case Count and Case Comfortability:

Right now only doing the first couple weeks and then when I get it working I'll expand to the entire row (16 weeks)

Here's what is charted:

So I highlighted just the Case Comfortability row and this is what I get:

I'm not sure why that is because it's numbers just like the other row.

I specifically take the "star rating" from another form and convert the number word to the digit number (i.e., "four" to "4") and they are being pulled from that cell to populate this row.

Any thoughts?

Genevieve P.

Hi @dhall

Can you post the formula you're using for this conversion?

It sounds like perhaps these numbers are being seen as Text instead of Numerical. This can happen if a formula returns the number with quotes around it, like so:

=IF(Data@row = "Four", "4")

You'll want to ensure any numerical outputs don't have quotes, like so:

=IF(Data@row = "Four", 4)

Let me know if this may be what's causing the error!

Cheers,

Genevieve

dhall

Oh I do have quotes around my numbers in the formula.... let me remove those and see if that's it.

dhall

😑

That was it:

@Genevieve P. , you are a rockstar! Now I get to go update formulas and FINALLY get my charts working the way I want them to. Thank you so much!

Genevieve P.

It's so great to see your chart working! Glad I could help, and good luck 🙂

Stephen_at_ODOC

I have a similar question/problem that the solution here won't work for. My "Week #" are the ROWS, not the columns. Because the data is added weekly, using a form, the Sheet adds the entry as the next row down — I don't see a way to have it add a column, instead. So in an identical situation to the above, how do I either convert the rows-to-columns or get the graph to understand that the weeks are rows instead of columns?

Genevieve P.

Hi @Stephen_at_ODOC

Weeks being the rows is no problem. If there's a date associated, that set-up would even let you use a Report to show a rolling 4-week chart that updates automatically, or something similar.

You should be able to simply select the rows you want to map in the chart, then select Smooth Line to have the X-Axis be the Weeks. Make sure that the Week date or name is the first column in the data set.

Cheers,
Genevieve

Stephen_at_ODOC

I've tried it using the actual date and again using the Week Number. I must be missing something, because no matter which way I do it, it comes out not as individual lines. It comes out looking like this:

I'm not sure why I'm getting dots instead of all of them connected together into a line, or why the dates that are identical aren't merging together, or how to get it to clump them into weeks (aside from adding the "WeekNumber" helper column and using that instead of a date — which combines them on the horizontal axis, but still doesn't create three distinct lines.

Stephen_at_ODOC

Update: If I sort the Report the chart is based on by the Sheet Name, instead of the dates, I get connected lines, kind of like I'm expecting, but each column is in its own section — they don't overlap as they should based on the dates.

These are three different sheets (maintained by three different people), all feeding into a single report, then dashboard. I simply want to "merge" the three you see here so the dates are all overlapping (by week, preferrably).

Brian_Richardson

@Stephen_at_ODOC Smartsheet charts aren't pivot charts, they are more simple numeric charts. So they will show the data as it is in your sheet without combining anything that's similar. If you have missing or blank data in some of those cells, the lines break up as you've noted. You need to do the combining first, using either a separate sheet with formulas, or the Pivot app, or a report that groups and summarizes.

The last option is often a good one, if you have a report that groups your data and summarizes it (SUMS or AVERAGES the numbers for each column), then you chart it, you'll get a chart of the first level of grouping with the grouping as the series (rows) and the summed/averaged numbers per group as your data.

You also have an option on the charts to flip rows and columns which can be helpful when your data is laid out as you describe.

So if it was me, I'd setup a report with your dates as the grouping, summarize the columns that you want on the chart, and then point your chart widget at that report. You may need to flip rows and columns to get it to look right.

Stephen_at_ODOC

Yes! Grouping everything by the Week Number did it! You're now in my top 25 list of heros!

Brian_Richardson

haha I'm so glad!

Genevieve P.

Hiya! I'm so glad to see this was resolved - thanks @Brian_Richardson for stepping in, and thanks @Stephen_at_ODOC for posting your solution. 🙂

Angie Hatfield

Hi, I am having a similar problem.

I can't get row two to show up in a line chart. Bar chart - fine. If I switch row two up to row one, it still will only show row 1 (which was row 2). If I select row two only, it shows. I tried copying a similar sheet but same result. I tried using a report, still nothing.

I have a summary sheet where row 1 comes from one sheet and row 2 from another. Same layout on both.

Works for bar charts, but not line chart. The legend will show row 2, just not the chart.

There are no formulas in the summary sheet. There are in the originals, but both have the same formula.

I can't figure it out and haven't found a discussion that solves this.

It will only show row 1, regardless of which sheet it is.