Question on graphing data on a dashboard while using a smartsheet form
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.
What is the best way to solve this and allow my chart to plot any new data point that shows up in the column?
Best Answer
-
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.
Answers
-
You can try Chart widget with source data is a report with the columns you need to show.
-
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.
-
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.
-
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.
-
Yes I think this will work for me! I appreciate you taking the time to work on this.
-
I'm happy to help you!🙂
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives