Multi-Line Chart from Imported Excel Data based on Counts Per Date

RyanAtPDI · 03/11/25

Hello! Got a problem that's confusing me because I'm used to Excel and can't find a way to do this on SmartSheets.

Context: I have a CSV that I export from a database that contains info about design issues such as ID #, Status, Create on Date, Updated on Date. I'm using Smartsheets as a project management tool and I'm trying to make some visualizations that show this data better. The data set has to be exported weekly and uploaded to smartsheets every time since there's no datalink currently without getting into the API of that platform (later problem)

What I'm trying to do: Create a multiline chart that shows the number of issues create on dates compared to the number of issues closed on dates. See image for both charts I'm attempting to combine. These charts are currently being created using 2 separate Reports. Top one has a filter that only shows issues with a "Closed" status and I pull the date from the "Updated on" date. The other chart is a summarization of all issues regardless of status with groups, and summarizations.

Specific Issue I'm having: I can't get these two reports into a multiline chart to see how the closed issues compare to the open issues. I was thinking I'd generate a list of unique dates from both columns, do a COUNTIF next to it, and then generate the chart from there. I just can't pull a unique set. These dates will also change as the project progresses so it's not like I'll have specific dates to choose. I supposed I could pull it by specific ranges such as weekly.

Example of rough formula: COUNTIF(date is between 1/1/2025 and 1/7/2025), and then do this for every date in the project. This is also a proof-of-concept dashboard that I plan on using on all my projects in the future so being able to apply it to more projects, potentially several years long, would be beneficial.

My head is kinda spinning from thinking too much about this and was hoping someone in this smartsheet world could help me out! Thank you in a advance!

Raw Data Export with lots of columns hidden:

RyanAtPDI · 03/12/25

I got it! Here's what I did: I have the data import from excel living in a grid untouched. I created another grid page and did what is in the image below. Created week names, week start dates, week end dates and then had two formula columns to do the counting.

Formula for # of Issues Created column:

=COUNTIFS({External Data Sheet}, >[Week Start]@row, {External Data Sheet}, <[Week End]@row)

Plain English explanation: Count the rows in my "External Data Sheet" if the Date column is greater than Week Start Date, and less than Week End Date

#####

Closed Issue counting was a little different because I needed another parameter since the status has to be closed for this one to be counted.

Formula for # of Issues Closed column:

=COUNTIFS({External Data Sheet}, >[Week Start]@row, {External Data Sheet}, <[Week End]@row, {External Data Sheet}, "Closed")

Plain English Explanation: Count the rows in my "External Data Sheet" if the Date column is greater than Week Start Date, and less than Week End Date, and Status column is set to Closed

########

Pic of the raw data for reference (with columns hidden):

#######

Chart Result:

Paul Newcome · 03/11/25

You will need to run the calcs on a separate sheet with formulas and cross sheet references. One column for dates, a column for open count, and a column for closed count.

RyanAtPDI · 03/11/25

@Paul Newcome! How do I generate the list of unique dates? Not every issue is closed too. So there would probably have to be a column for "Created Date", "Status", "Updated Date", "Open Count", and "Closed Count".

So I'd have a list of dates with counts associated with each date like this:

"Open Count" column formula would be =CountIf(StatusColumn="Open", else("")

"Closed Count" column formula would be =CountIf(StatusColumn="Closed", else("")

How do I make that list of unique dates?

Paul Newcome · 03/11/25

It depends on what you want to see. Are you just trying to chart out which dates have been selected, or would you want something more consistent such as daily, weekly, or monthly, or would you rather have a rolling period such as a rolling 12 months (on a monthly basis) based on today's date?

RyanAtPDI · 03/11/25

The dates will come in somewhat randomly on the days I preform design reviews. They won't be consistent.

Paul Newcome · 03/11/25

Ok. I understand that, but you can make your chart labels more consistent so that it shows more like a timeline view, or you can just plot the dates as they are with the same spacing between dates whether it is days or weeks between two separate dates.

RyanAtPDI · 03/12/25

@Paul Newcome I wasn't thinking about it that way, but I suppose I could do a ranged view setup by weeks

Week 1 = 3/9/2025-3/15/2025; Week 2 = 3/16/2025-3/22/2025; etc.

Could you suggest how I could go about setting that up?

RyanAtPDI · 03/12/25