I posted this in the Forum a week ago and haven't had a response. I'm hoping someone in ATUG can help me figure this out. I've exhausted every solution I've been able to find in the Forums. Here is the case:
I am trying to determine the Average Cycle Time for two different calculated periods.
The first period is the difference between [Date_Quoted_To_Customer] and [SOIDATE]. The second period is the difference between [OppHeaderCreatedDate] and [RSDAPPROVALDATE].
The first period is called [Bid to Close Days] and I used this formula to calculate it: DATEDIFF('day', MAX([Date_Quoted_To_Customer]),MAX([SOIDATE]))
The second period is called [Deal Qual Days] and I used this formula to calculate it: DATEDIFF('day',MAX([OppHeaderCreatedDate]), MAX([RSDAPPROVALDATE]))
I want to show the current Average value of each of these calculated periods based on some display and filter criteria. I created separate WINDOW_AVG calculations for each of the periods and the result does not match my check that I did separately in Excel.
I should clarify the AVERAGE calculation I'm looking for: I have created a filter called BID STAGE. I want to calculate the Average Bid to Close Days and Average Deal Qual Days for the records in each BID STAGE, by SOIDATE year. That is where I'm stuck. Every solution I have tried doesn't match what I calculate separately using the AVERAGE calculation in Excel.
I'm sure that is has something to do with the fact that my cycle time calculations are aggregations but I'm at a loss on how to work around it. My ultimate goal is to create a KPI dashboard for some Sales metrics and these two Averages will be part of the criteria for some of them.
Thanks in advance for the help!
Average Days check.zip 4.7 MB