Dashboard - Graph of Report Data Over Time?

Riza Khan

I'm struggling to create something that seems simple at face value.

The sheet "Animals" is a ledger with the following columns:

Date, Animal Type, Breed, Count

2/4, Dog, Pomeranian, 2

2/5. Dog, Pomeranian, 3

Each day a new line is added via a form for each unique animal type and breed, and we count all animals every single day.

If I want to graph the rise and fall of the count of "Pomeranians" over time I'm creating a helper sheet with one column per line I want to show up on the sheet.

We then populate the first column of the helper sheet with every date and have it pull data from the "Animals" sheet via an SumIF formula.

Is there a better way to do this? I feel like this requires a ton of maintenance and leaves a ton of room for error. In excel I could simply create a pivot table or chart in seconds. How can I mimic that on Smartsheet?

Abhiraj Joshi

Hi Riza,

One way I can think of is, create an automation to copy rows to another sheet with the condition for breed (e.g.: Pomeranian).

Trigger: When rows are added or changed. (Date changes to 'Any Value')

Condition: When Breed is one of "Pomeranian".

Action: Copy rows to another sheet.

So every time if an entry is made, the row will be copied to that sheet. You can then use that sheet to create a line chart. You can also put additional columns and formulas in that sheet for any other analysis.

If it's possible to share a screenshot of your helper sheet (without sharing any confidential/private data), that will give a better idea as to how it can be improvised.

I hope this helps.

Regards,

Abhiraj

Riza Khan

This is a great workaround that I'll need to explore.

With this I will be able to plot a line chart for Pomeranians over time. The challenge I see here is how do I combine the line charts for one chart with Dogs? Would I do that by running a report?

Tony Rice

Any suggestions in handling charting data like this? I've got a line graph visualizing data that grows daily (an API writes a new row each day).

Right now I'm editing the dashboard periodically and adjusting the range of rows and columns, including as many empty rows at the end as are available. This saves from having to edit the dashboard daily, but still runs the risk of missing data and is just a pain to have to remember to do.

Is there a better way to accomplish keeping a chart up to date as data is not only updated but added? Perhaps updating dashboard objects via the API?

Kevin Smith

This would be a great feature to suggest having added. If I were going to try this, I think I would have to use a third party tool (Tableau or maybe Google) to read the sheet and produce the graph and then insert the graph using the Web Content Widget. I can't say if that would work, I have not done it but it sounds like it would be able to pull in the full sheet daily and graph it. I would love to know if you solve this.