Create a Chart on Dashboard to show Year over Year by a Line Chart

So I have 2 data sheets, and I created one report, that shows Year, Day, and volume. I have grouped the report by Year, and Summarized with a sum by the total I want to return. I have also tried grouping by Day to get the summary total by day I want. Here's my problem - when I go to do a Line Chart it just shows one line with my summary group as the key - I want to show this like I would in Excel - 2024 as one line, 2025 as another line, with each showing their respective total by the day of the month (example 176 on 1/2 for 2024, and 105 for the same day in 2025). Also, instead of every day - can I auto do like every 7th day? Note if I click switch rows/columns I get a bunch of dots for each day in my legend and it has no line. What am I doing wrong? Below are screen shots of my attempts:

Find more posts tagged with

Accepted answers

Jgorsich

In addition to the report (the last dashboard I made had 4 different reports and 3 different helper sheets in addition to the actual data collection sheets).

So, you'd make an additional sheet that had your dates that you'd want to graph in one column and then you'd have the years as separate columns next to it (note, you don't have to devote the entire column to this - my helper sheets end up getting pretty messy because after doing something like this I'll just scroll down a bit for the next thing I'm graphing). In the 2024 column (or cells) you'd have formulas that summarize the volume you'd want graphed against that date (so, if you want to graph by week, you'd have a formula to summarize by week number). Then on your dashboard you'd graph that data from the helper sheet.

Might look like this:

Mind you, this is a lot easier if you add a column to your original data sheet that uses the weeknumber() formula to give you a week number (or month, or however you want to group it) - then you don't have to try to extract just the day and week from a date that will be in the wrong year to compare against (week 1 will always be week 1, in 2024 and 2025… but January 15th will be AFTER 1/7/25 and BEFORE 1/7/24, so going with week numbers instead of "January 7" makes life a little easier). If you REALLY want it tied to calendar dates instead of week numbers you can use Yearday() to extract it and compare against that instead.

Smartsheet's graphing engine is pretty limited in terms of what kind of data it can parse- there are LOTS of situations where organizing your data beforehand in a helper sheet is absolutely critical to making the dashboard look the way you want it too.

All comments

Jgorsich

The only way I've found around this is to make a helper sheet that grabs the data that you want into a table that is then easy to graph on your dashboard - I usually end up having 1 or 2 helper sheets for every dashboard that I make purely for organizing data into a format that the graphing engine can recognize.

Jenn F.

So is that not the report I created as a helper? Or how would I set up a helper sheet? Any examples you can share?

Jgorsich

In addition to the report (the last dashboard I made had 4 different reports and 3 different helper sheets in addition to the actual data collection sheets).

Might look like this:

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.