Creating a Trend Chart
Hi,
I've created a form for users to record tasks, which is recorded in a sheet with the following fields:
Date User, Task
The Date field is at date/time field. I may need the time, but for reporting, i only need the date
I would like to have a chart in a dashboard that will summarize the number of tasks recorded by day
Ideally it would look something like:
How do create such a chart?
Thanks,
Andrew
Best Answer
-
Hi @Andrew T ,
Here's how I do these, especially with the new Grouped Report data for charts!
For the sheet, I made a column called Period. This just converts the Date Column to text so that I can use that in the grouped report without having any weird numeric values. The column formula I used is as follows:
Adding double quotes converts the date to text.
Then I create a grouped report that groups by my Period column, and summarizes my Activity column by summing values:
From there I made a chart on a dashboard sourcing my grouped report with the following settings:
Using reports is a great way to use for source data on charts, since it will automatically adjust your range if new rows are added over time. Hopefully that helps, I'd love to see some other ideas from the users!
Answers
-
Hi @Andrew T ,
Here's how I do these, especially with the new Grouped Report data for charts!
For the sheet, I made a column called Period. This just converts the Date Column to text so that I can use that in the grouped report without having any weird numeric values. The column formula I used is as follows:
Adding double quotes converts the date to text.
Then I create a grouped report that groups by my Period column, and summarizes my Activity column by summing values:
From there I made a chart on a dashboard sourcing my grouped report with the following settings:
Using reports is a great way to use for source data on charts, since it will automatically adjust your range if new rows are added over time. Hopefully that helps, I'd love to see some other ideas from the users!
-
Thanks for this Chris, it worked like a charm!
-
@Chris Mondeau - would this idea work if I had three different dates also?
-
@Peggy assuming you may be using 3 different date/period columns, then yes. They would be overlaid as different series in the plotted graph.
Alternatively, if you're trying to count values that lie between 2 dates, then you could do that as well.
If you post a snapshot of the data you're trying that could help. Thanks!
-
The trend I'm looking to chart is: in what order are the candidates taking the test.
I can't seem to get things setup correctly to use a report with a chart. I keep getting an error that I can't use that format.
Below is a screenshot of my sheet. I used your formula from above to convert the date to text (orange highlighted columns):
=[Exam Scheduled (Date of): Test 1]@row + ""
Ideally I'd like to come up with a formula that would tell me what order as well (blue highlight columns). I'm currently manually entering the number into the Order columns & updating the formula for the two columns: # Days between 1st & 2nd Test & # Days between 2nd & 3rd Test
I have a formula in the AVG Between column:
=IFERROR(AVG([# Days between 1st & 2nd Test]@row, [# Days between 2nd & 3rd Test]@row), "")
This is more just a metric data point in a Summary Sheet: Overall AVG Days between.
Any assistance would be appreciated.
Thanks -Peggy
-
This has worked for me. How can i possibly convert it into a stacked graph? as below
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 351 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!