I'm wanting to graph the number of days requests are waiting for customer response per month.
So, if a request was closed in June, I want the number of days it spent waiting for the customer.
Thanks in advance to all those brilliant minds out there!
Hello @JSpears
The brackets you are using { } are to reference ranges and not individual cells or rows.
By adjusting the function you have showing, it should look closer to this.
=IF(MONTH([ACM_CL_ClosedDate]@row=6,NETDAYS([ACM_CL_DateRequestClose]@row,[ACM_CL_ClosedDate]@row))
Although, you wouldn't need the IF statement unless you only want values for that month, which can be a pain when graphing because if you want to change the graph in the future, you have to change the function.
This part alone would be enough as a column function. In a report you can filter by the month and is more manageable than changing the function to view a new set of data.
=NETDAYS([ACM_CL_DateRequestClose]@row,[ACM_CL_ClosedDate]@row)
Thank you, I'm actually using the {} because I'm referencing columns in another sheet. I'm thinking that I will actually need to add a NetDays column to the source sheet and then pull that into my metrics sheet....
@JSpears
NETDAYS only allows individual values to be used, and not ranges. You can use a lookup function to analyze a range and pull a single value. Index/Match would work, or Index/Collect if you have more than 1 criteria.
=IF(MONTH(INDEX({ACM_CL_ClosedDate},MATCH(Lookup@row,{Lookup Range},0)))=6, NETDAYS(INDEX({ACM_CL_DateRequestClose},MATCH(Lookup@row,{Lookup Range},0)),INDEX({ACM_CL_ClosedDate},MATCH(Lookup@row,{Lookup Range},0))))
ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.
