Average calculation results are incorrect
Brooke Bondur Jul 8, 2013 7:55 AMI 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 have gone through the Forums, Knowledge Base and Release Notes to find a solution and have made multiple attempts to fix this based on my findings but I have had no success. I'm hoping that someone is able to solve the mystery for me. 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!
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.
Message was edited by: Brooke Bondur

Average Days check.zip 4.7 MB