![]() Once we apply this using the ‘Filter data’ option in Prep, we’re left with 12,186 rows instead of the 3,145,728 we had before. You don’t have to manually add a record for every day. The date_dim table extends into the future, which is good. Finally, part three trims out any dates that haven’t happened yet.Part two trims dates post termination date.Part one trims dates prior to an employee’s hire date.To correct this, in the next step, filter out any unnecessary records with a calculation: ![]() ![]() Apply this to hundreds of employees, and you’re left with a huge data set. You’d have a record for every single day, running back to 1964, when you only need 30 records. Say you worked at a company for 30 days, but your date_dim table extended back to 1964, the year your company was founded. Now we can calculate measures like ‘average daily headcount’ rather than only being able to consider the headcount at the end of the year.īut, we are also left with many extraneous records. This allows us to ask the more complex questions necessary for an accurate turnover rate. This is helpful because we’ve changed the granularity of our date field and now have a record for every day that an employee worked at a company. This duplication occurs because we are joining every row of our first table to every row of the calendar table. Since the field we are using as our join key is just the number 1, the resulting table is going to have a much higher row count: 3,145,728 to be exact. Now you can join the two tables together. Next, connect to the date_dim table and do the same thing-create a dummy key field to join your fields on. This calculation will allow you to join to the date_dim table. In your first Clean step in Tableau Prep, create a dummy calculation: Start by bringing in your headcounts table-the table with one row per employee. Here is what the finished Prep flow looks like. But, you can also just create an Excel file that has the dates you need. If you’re connected to a database like SQL Server that is managed by a database administrator, chances are this table already exists in your database (they’re a standard at most organizations). In order to scaffold, you need a date dimension (date_dim) table-a table that contains one column, with one record per day and no missing dates. Our restructured data will look more like this. In other words, you need a record for every day that an employee worked at the organization. You’ll need a record to represent employee tenure at the daily level. You will need to restructure this data to understand how each date relates to an event. But this structure isn’t ideal for analysis in Tableau. Each employee gets one record and that one record contains all their information. This data structure makes sense from a database perspective because it's efficient and takes up little space. ![]() Two additional columns provide the employee’s hire data and termination date, and the termination date is NULL if the employee still works at the organization. A common way to store data about employee tenure looks like this:Įach employee at the organization is assigned an employee ID. Reference Materials Toggle sub-navigation.Teams and Organizations Toggle sub-navigation.Plans and Pricing Toggle sub-navigation.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |