nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Past 3 months info graph in a dashboard

figuringitout

Hello,

I am looking to project an automated graph that shows the past 6 months in a dashboard. I have a sheet that has a date column with information from the past two years. Also, three different descriptions "Expedited", "Redo", and "Expedited and Redo". I used this formula to get the date from the past 6 months as a helper row:

=IF(AND(ISDATE(Date@row), Date@row >= DATE(YEAR(TODAY()) - IF(MONTH(TODAY()) <= 6, 1, 0), IF(MONTH(TODAY()) <= 6, MONTH(TODAY()) + 6, MONTH(TODAY()) - 6), DAY(TODAY()))), Date@row, "")

How can I create one graph (stacked bars preferably) that counts the number of "Expedited", "Redo", and "Expedited and Redo" data for the past 6 months? and that updates automatically?

I can have multiple graphs for each description.

Find more posts tagged with

Sheets

Dashboards

Project Management

Accepted answers

All comments

Nico Mahnke

Hi!
A clean way to achieve this is by using a Pivot Table + Report + Dashboard chart setup.
Step 1: Create a Pivot Table

-Use your original sheet as the source.
-Use the Rows of a column as your date column and aggregate it at the level you want (days, weeks, or months).
-In the other Columns, use the description field with your three values:
Expedited, Redo, and Expedited and Redo.
-Set the Values to count records.
This already gives you your X‑axis (counts per description) and Y‑axis (time).

Step 2: Filter to the last 6 months

Since you already have a helper column that flags dates from the past 6 months, you can base a Report on the Pivot Table.
Filter the Report so it only includes rows where your helper logic applies.
If needed, you can add an extra checkbox flag in the Pivot source to make filtering even easier.

Step 3: Build the Dashboard chart

Use the Report as the data source for a stacked bar chart in your Dashboard.
Because the helper formula is dynamic, the Pivot, Report, and chart will all update automatically as time moves forward.

This keeps everything automated, avoids manual date updates, and gives you one stacked graph showing all three categories for the last six months.

Hope this helps! 🙂

Nico Mahnke

Paul Newcome

You should be able to just create a row report. Filter it to only show rows in the past 6 months, group it by description, then use the summarize feature to get the totals for each.

Use this report to populate your chart.

figuringitout

I tried the report way but, for some reason I can't create a graph of that report.

Paul Newcome

Did you group and then summarize the report? Are you able to provide a screenshot?

SueinSpain

I've found just creating a row report works for me.

I use the formula in the smartsheet and have a True / False option in a helper field to identify this month, last month, last 3 months, this financial year etc. (one firld for each). The I can just select on = True for the ones I want.

Row report into chart widget on dashboard

Good luck

this is just some examples

This Month (2)

=IF(AND(MONTH([Date & Time]@row) = MONTH(TODAY()), YEAR([Date & Time]@row) = YEAR(TODAY())), "True", "False")

Last Month(4)

=IF(OR(AND(MONTH([Date of Data]@row) = MONTH(TODAY()) - 1, YEAR([Date of Data]@row) = YEAR(TODAY())), AND(MONTH(TODAY()) = 1, MONTH([Date of Data]@row) = 12, YEAR([Date of Data]@row) = YEAR(TODAY()) - 1)), "True", "False")

Last 3 Months(4)

AND(IFERROR(MONTH(@cell ), 0) = MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1), IFERROR(YEAR(@cell ), 0) = YEAR(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1)))

u.brinkrode98571

Dear Paul,

Does this work if you have a column with multiple entries?

To solve this I created a Metric Sheet and used the =Has() formular in regards to a column header that changes automatically (Month and Year).

Have a great day

Paul Newcome

@u.brinkrode98571 You cannot group a report by a column that has multiple selections allowed, so yes. You would need a second sheet, and you would need to leverage the HAS function.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.