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.
Regards
Greg
Hi Tom
think i have this cracked but would like an opinion.
I used LODs to create the first date and the second date required then did a datediff between these 2 and averaged the answer and from what I can see it seems to work.
The validation i am doing is looking at the results at a row/line level and averaging the the Total from this answer then putting in my new calc and the answers match so I am happy that it looks to be working and allowing me to filter across various options :BU, product, new/renew etc and the answer looks solid.
As i said I would mind and opinion on it.
LOD 1: Create Date = { include [Opportunity ID] : min([Createddate])}
LOD 2: Close Date ={ include [Opportunity ID] : max([Closeddate])}
Calc 1: Days Delta = DATEDIFF('day',[Create Date],[Close Date]) --------average this result
Thanks
Greg
