Question on graphing data on a dashboard while using a smartsheet form

Jared P · 11/05/24

Hello,

I am trying to graph data for production as close to real time as possible. We run a test and then use a form to populate a smartsheet. I have a dashboard pulling from that smartsheet to graph the data. I want the graph to populate based on new data entries, however the graph will only populate based on the data I select and won't continue down the sheet with new submissions.

Current Issues:

The smartsheet uses automation. As soon as a cell is changed the sheet automatically adds in upper/lower limit and targets, which also get plotted.
The main problem is that I can only plot the data that currently exists and the dashboard won't plot new entries as they added to the smartsheet. In the screen shot my data is being plotted down to row 5, but won't plot row 6 that was added without manually making the chart plot row 6.
Capture.PNG

What is the best way to solve this and allow my chart to plot any new data point that shows up in the column?

Thien Huynh · 11/06/24

This is my idea:

First: your sheet may have 3 more columns like this picture: RowID, RID and Report Filter

with the RID fomulas is:

=MATCH(RowID@row, RowID:RowID, 0)

and the Report Filter column is:

=IF(AND(Row# = "1-5", RID@row <= 5), 1, IF(AND(Row# = "6-10", RID@row >= 6, RID@row <= 10), 1, IF(AND(Row# = "11-15", RID@row >= 11, RID@row <= 15), 1)))

and a sheet summary field "Row" with type Dropdown like the picture below

and then you can create report with filter like this

and this is your dashboard looks

I hope this will solve your problem.

Thien Huynh · 11/06/24

https://community.smartsheet.com/discussion/132898/question-on-graphing-data-on-a-dashboard-while-using-a-smartsheet-form

You can try Chart widget with source data is a report with the columns you need to show.

Jared P · 11/06/24

https://community.smartsheet.com/discussion/comment/461055#Comment_461055

That does seem to work, but it comes with its own limitations. It forces me to plot the first 500 rows, which isn't awful, but what happens at row 501? No more data points I assume and I would need to archive the sheet, create a new one and start over.

I was hoping for a more dynamic way to tell the program I want to plot rows 1-40, now plot rows 41-80, now plot 81-120.

The other issue with plotting 500 points is the graph will be very squished together after I get 500 points plotted.

I do appreciate the help, and I might need to do it this way if I can't figure out another way.

Jared P · 11/06/24

https://community.smartsheet.com/discussion/comment/461055#Comment_461055

Unfortunately using the report as the source will not work. As I feared it is way too many data points to have be useful. Here is what 500 data points looks like versus 40.

This is 40 data points

This chart is 500 data points.

Thien Huynh · 11/06/24

This is my idea:

First: your sheet may have 3 more columns like this picture: RowID, RID and Report Filter

with the RID fomulas is:

=MATCH(RowID@row, RowID:RowID, 0)

and the Report Filter column is:

=IF(AND(Row# = "1-5", RID@row <= 5), 1, IF(AND(Row# = "6-10", RID@row >= 6, RID@row <= 10), 1, IF(AND(Row# = "11-15", RID@row >= 11, RID@row <= 15), 1)))

and a sheet summary field "Row" with type Dropdown like the picture below

and then you can create report with filter like this

and this is your dashboard looks

I hope this will solve your problem.

Jared P · 11/06/24

https://community.smartsheet.com/discussion/comment/461091#Comment_461091

Yes I think this will work for me! I appreciate you taking the time to work on this.

Thien Huynh · 11/07/24

I'm happy to help you!🙂

Question on graphing data on a dashboard while using a smartsheet form

Best Answer

Answers

Categories