Creating a Trend Chart

Andrew T

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

Chris Mondeau

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!

Chris Mondeau

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!

Andrew T

Thanks for this Chris, it worked like a charm!

Peggy Parchert

@Chris Mondeau - would this idea work if I had three different dates also?

Chris Mondeau

@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!

Peggy Parchert

@Chris Mondeau

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

test metric sample.png

Stanzozo

This has worked for me. How can i possibly convert it into a stacked graph? as below