Aging Date Average

Beemer Bum · 10/31/18

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?

Paul.Koetke · 11/01/18

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

Beemer Bum · 11/01/18

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!

Aging Date Average

Comments

Help Article Resources

Categories