Histogram on days past

pdxhunley

I'm trying to use a sheet that i have created to capture issues; but I'd like to create a histogram of days past of when they've been discovered.

7 Days past

14 Days past

30 days past

Etc.

Scott Peters

Hi @pdxhunley - This should be very easy to do:

1. Add a column in your sheet with a column formula such as =IF([Discovery Date]@row < TODAY(-30), ">30 Days", IF([Discovery Date]@row < TODAY(-14), ">14 Days", IF([Discovery Date]@row < TODAY(-7), ">7 Days", "This Week")))
2. Here's an example of what it would look like:
3. Create a Smartsheet Report of that sheet, with a GROUP by the aging period
4. Create a Chart Widget in your dashboard that points to the report you created

This is just one way to accomplish this, but I hope it is helpful to you

pdxhunley

@Scott Peters

Thank you for that. I'm getting an error on this formula. It's only populating on some of the dates.

=IF([Date Discovered]@row < TODAY(-120), ">120 Days", =IF([Date Discovered]@row < TODAY(-90), ">90 Days", =IF([Date Discovered]@row < TODAY(-60), ">60 Days", =IF([Date Discovered]@row < TODAY(-45), ">45 Days", =IF([Date Discovered]@row < TODAY(-30), ">30 Days", =IF([Date Discovered]@row < TODAY(-14), ">14 Days", =IF([Date Discovered]@row < TODAY(-7), ">7 Days", "This Week")))))))

Scott Peters

Hi @pdxhunley - On the day that you posted this, 6/22/23 was exactly 120 days apart from "today". Try changing from less than to less than or equal to:

Mary McCabe

Is there a way to create this formula for between two time frames to make the histogram?

Between 1-10 days; 11-20 days, 21-30 days, etc etc?

Appreciate any info!

Mary McCabe

@Scott Peters I was able to follow your steps to create a column with bucket labels and then create a grouped report. However, my dashboard is not allowing me to create

a graph from this report. Any ideas?

Scott Peters

Hello @mary twomley - To answer both of your questions…

Depending on how many periods you have, it may be easier to add a helper column to capture the difference between today's date and the date you are attempting to reference. In this example, my formulas are:

=IF(Date@row >= TODAY(), 0, (TODAY() - Date@row))

=IF([Days in the past]@row > 30, "31 Days+", IF([Days in the past]@row > 20, "21-30 Days", IF([Days in the past]@row > 10, "11-20 Days", IF([Days in the past]@row > 0, "1-10 Days", "Current"))))

The order in the formula is important: First we evaluate the worst case scenario, then we evaluate the 2nd worst case scenario, and so on, until the final condition of the last nested if statement is "Current"

For the dashboard histogram graph, your report will need two things: Group by your aging/period column, and then a summary (count) of your aging/period column. Like this:

When you're done, the report should look something like this:

And then your dashboard can support the graphic:

I hope this helps!

swfascetti

This is very clever. I was doing this with the Pivot app and / or COUNTIF using a metric sheet. Didn't know one could create a chart off a report. Very Nice!. @Scott Peters would love a way to for an order without tricking it to alphabetize. See charts: