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

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

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

• ###### 1. Re: Close rate in Days- Salesforce.com Calculation- need help

Hi Greg,

Please attach a Tableau Packaged Workbook including some sample data so we can replicate the scenario. Or, if you cannot attach it all, create some sample data and lay out the details for the new scenario based on the sample data.

• ###### 2. Re: Close rate in Days- Salesforce.com Calculation- need help

Hi Tom

thanks for this

please see attached a sample workbook and an excel file showing what I need the calculation to do for me.

Cheers

Greg

• ###### 3. Re: Close rate in Days- Salesforce.com Calculation- need help

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

• ###### 4. Re: Close rate in Days- Salesforce.com Calculation- need help

Looks good to me Greg. I'm glad you got it solved.