Histogram on days past
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.
Answers
-
Hi @pdxhunley - This should be very easy to do:
- 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")))
- Here's an example of what it would look like:
- Create a Smartsheet Report of that sheet, with a GROUP by the aging period
- 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
-
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")))))))
-
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:
-
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!
-
@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?
-
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!
-
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:
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!