3 Replies Latest reply on Nov 6, 2015 6:31 AM by Jeffrey Johnson

# calculated field count within date range

Is is possible to have a moving calculcation of count within a date range?   For example, I am calculating employee turnover.  I have a large flat file that includes hire date, termination date, and employee information.  I want to be able to tell how many active employees we had in a particular timeframe (i.e. January 2015) based on a count of anyone hired prior to 1/1/2015 on the condition that their termination date is AFTER 1/31/2015.

I need to do so I can analyze turnover by multiple dimensional slices so I think it needs to be a Tableau calculation rather than using excel.

The "final" calculation will be number of terminations divided by the number of active employees for a specified date range and various dimensions.  It would be most beneficial if the date range could be easly adjusted to see how the number smoothes over time to create rolling year calculations.

• ###### 1. Re: calculated field count within date range

Hello Jeffery,

The first thing you need to do is implement a Master Date so that you can show these counts on a centralized time scale. Tableau cannot "create" records (Which you need to do in order to show how many employees were active during a particular month). So the only way we can do this is either by manipulating our Data on the backend, Manipulating the Joins, or using Data Blends (But the latter greatly limits flexibility).

Here is an example of one way to do this

Combining Start and End Dates into a Single Axis | Tableau Software

Regards,

Rody

1 of 1 people found this helpful
• ###### 2. Re: calculated field count within date range

I wrote this piece on dealing with such a case

Vizible Difference: Taking Stock with Start and End Dates

1 of 1 people found this helpful
• ###### 3. Re: calculated field count within date range

Thank you all - this is very insightful.  I'm working through all of the suggestions and will post back with what works the best.  Rody, I think your idea to create a master date is a good one.  I did it with a table of each day per Kettan's warnings.  I must have messed something up because the count didn't turn out to be accurate so I need to keep working that and find my error.  Alexander, I think your scaffold approach makes good sense and may try that as a next solution as well.

I'll post back when I have something useful to share!