4 Replies Latest reply on Aug 15, 2016 5:34 AM by Tom W

    Close rate in Days- Salesforce.com Calculation- need help

    Greg Blackshields

      Hi all


      trying to create what i think should be an easy calc but does not seem to be working for me.


      Summary: Need to create a calculation that will show me the average close rate in days of all my Closed Won Opportunities from Salesforce.com


      Start Point: Created a DateDiff calc between Create and Close date : datediff('day',min([Createddate]),max([Closedate]))

      This works fine as it shows me at a line/row level for each Opportunity how long it took to close


      Issue: when i wish to aggregate this back up to allow me to filter by say Sales Segment, Country, Product, Seller etc -the aggregation does not seem to give me the right average number


      Example : 29 Opps with total days of 4616- the average in this case is 159.2

      When I add in a Column total for all this Opps, shown on the Worksheet, and then use a Total using Average I do arrive at 159.2

      The issue is when I then remove the row level details from the view : Opportunity, Create date and Close date, the average seems to go to 991


      I then created a 2nd calc :  avg(datediff('day',[Createddate],[Closedate]))

      which does allow me to filter as required above - problem here is that the Total on this is 196.85 and when i use average on the total i again arrive at 159.2 and when i remove the row level details it reverts to 196.85 and when i try to change the Total to using Average it stays at 196.85


      For me it would seem a simple calc but one that is stumping me.

      Unsure if i need to use an LOD- even though all the data i need is in the view or if it is a secondary calc off the original or even just a simple aggregate on the view but whichever way i  look at it the number i need, in this case 159.2 does not calculate.


      Apologies if this question or a similar one has been answered but any help appreciated.