Aging Date Average
Hi Community,
I am attempting to create a KPI for a dashboard that will capture the average number of days a 'something' has been opened, but only if it is still in an opened state. I can perform the appropriate "days open" with "=IF([Closed Count]3 = 1, NETWORKDAYS([Submitted Date]3, [End Date]3), NETWORKDAYS([Submitted Date]3, TODAY()))" and that works as expected. But, If I want to use column logic to aggregate, it all goes in the can.
So, I have 3 columns to deal with for criteria and/or data:
Request Type - has several options of which only two are what I care to use
Open Count - binary value that allows me to identify if open or not
Days Open - the number of days open per the calculation above
What I'm trying to do is:
=IF(OR([Request Type] = "Value 1", [Request Type] = "Value 2") AND [Open Count] = 1, AVE([Days Open], "0")
or something like that. I know the syntax is incorrect...consider it pseudo-code'ish.
What I should end up with is a single number representing the average number of days a set of requests have been open, for those that are currently open.
Thoughts?
Comments
-
Hi Beemer Bum,
You're pretty darn close with that formula. You're going to need a calculation column to do this. That calc column will need the formula:
=if(and(or([Request Type]="Value 1", [Request Type]="Value 2"),[Open Count]=1),[Days Open]
This column will then have the Days Open numbers for Value 1&2 items that are currently open. Now all your have to do is get an average of this calculation column. I recommend indenting all these rows and getting the average with:
=avg(children())
Then this average will be the number you post in your KPI dashboard.
Let me know if this does it.
Paul Koetke
Upwork - Smartsheet Consultant -
Hi Paul,
Thanks for the write-up. I'll take it for a spin and let you know how it drives.
Cheers.
UPDATE: it worked...mostly. There is only one column that can be indented in any particular sheet and it happened that it was none of those I shared. However, using the new aggregated column, I just average that since the correct values are there.
Paul, thanks so much for the quick and thorough reply!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 383 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!