3 Replies Latest reply on Mar 13, 2015 12:26 PM by Zach Leber

# Start and End date calculations

Hi,

I'm trying to make a graph that shows that change over time of how long it takes to perform a certain process. Each process has multiple steps, and I need it to show the length of time it took for the whole process to be completed in the month it was completed. So, for example:

CompanyStageStart DateEnd Date
A11/1/151/4/15
A21/5/151/8/15
A31/9/151/15/15
B11/1/151/31/15
B22/1/152/13/15
B32/14/152/17/15

This would show

JanuaryFebruary
Days1447

Since Company B took 47 days and closed in February and A took 14 days and closed in January.

I think my problem is that whenever I put End Date on the x-axis, it filters only the results with an end date in that month. Meaning I need to use the start date of company B stage 1 in February to calculate properly but it gets excluded from the figures that get passed to February.

Somehow I need stage 3 to be able to calculate the end date of stage 3 minus the start date of stage 1 before it gets filtered by dates. Alternately, I guess I could create a calculated field that would give all stages a new date that equals the end date of stage 3 so that it'll get filtered into the correct month. Or maybe there is a table calculation that would solve this?

Thanks in advance for any help!

• ###### 1. Re: Start and End date calculations

Hi Dan,

Would it be possible to prepare some sample data and attach tableau workbook.

Regards,

MV

• ###### 2. Re: Start and End date calculations

Sorry. Thought I had. Attached now.

• ###### 3. Re: Start and End date calculations

You may be able to do this without table calculations. Here I create new two calculated fields:

[Elapsed Time]=MAX([End Date])-MIN([Start Date])

[Completion Month]=DATENAME('month', MAX([End Date]))

You still need to put the [Company] field in the view, though with Tableau 9.0 you may be able to use the new LOD calculations to include [Company] implicitly though I don't know how you want to display multiple projects completed in the same month.

Image below and modified 8.2 workbook attached.