3 Replies Latest reply on Sep 14, 2015 11:07 PM by Elizabeth Bingham

# Calculating Headcount and Attrition - Day/Week/Month/Year

Hi All,

I am stuck with a Calculation, I am able to see the Year on year Headcount and Attrition using the parameter in the Attached Spreadsheet.

My problem is .. I wanted to have a view of all the years since the first hire till date.. and I should be able to drill down from Year to Quarter to Month to Week and Date...

Unfortunately I do not have a an Calendar... and Even if I attach I am not getting the trick to accomplish the result..

I am attaching a Sample workbook.. please advise as how this can be done.. Thanks in advance.. :-)

kettan Joe Oppelt  (Can you please look into it) :-)

Thank you!!

Regards,

• ###### 1. Re: Calculating Headcount and Attrition - Day/Week/Month/Year

The attached uses method #2 in  The Cross Join Collection

```SELECT *
FROM [data\$] d, [lookup\$] l
WHERE l.[date] >= d.[Hire Date]
AND l.[date] <= CDATE(IIF(ISNULL(d.[Term Date]), DATE(), d.[Term Date]))

```

The attached spreadsheet has a lookup table of each date between Jan 1, 2002 and Dec 31, 2014.

The cross join increases number of rows from 103 to 182543.

Using extract increased the attached workbook from 22 KB to 55 KB.

The spreadsheet was opened with the Legacy Connection.

.Le

• ###### 2. Re: Calculating Headcount and Attrition - Day/Week/Month/Year

How kettan did this is how I'd do it...the reason why is that you want to "count" each employee N times, where N is the number of years/months/days that employee is active, and the easiest way for Tableau to do that is to give it enough records.