I have two data sources. The first is a record of each time an employee posts time along with what contract number they posted that time to. The second is a record of employee terminations. My company is a construction company that has people constantly being terminated and rehired. I need to figure out how to match the termination date to the nearest payroll date that is before the termination date so that I can identify what the last contract was that they posted time to. The best I've come up with so far is a level of detail calculation that finds the max payroll post date, but that doesn't account for those who have rehired and posted time after their termination date. In the tables below I'd need to match the 1/6/2016 date in the first table with the 1/5/2016 date in the second and also match the 3/6/2016 dates in both tables.
EmployeeTerm Date11/6/201613/6/2016
EmployeePost DateContract11/1/2016123311/2/2016123311/3/2016123311/4/2016123311/5/2016123313/4/2016232213/5/2016232213/6/20162322
Hi Mike,
Thanks for the post. I was able to accomplish this with a three step process.
1. Inner or Left join the two datasources, which creates duplicate records, but this will not cause an issue.
2. The first calculation returns the MAX Post Date if the Term Date is the MAX Term Date. Otherwise, return the Post Date if the DATEDIFF between Post Date and Term Date is greater than or equal to 0.
3. The second calculation returns the MAX of Calc1 based on Employee# and Term Date:
Ending result:
I'm attaching the workbook using Tableau 10.1
Let us know if this gets you your desired result.
Walt
