1

I'm new to Tableau! I hope this is a simple answers. Thanks in advance!

I'm working with employee data and I need to create a matrix of headcount totals across years and months.

Final Matrix Output Example

I'm starting with 6 tables listing all active employees at the beginning of each year from 2015 through 2020. I then have a list of employees and the date that were hired; so all employee additions. I then have the same thing for terminations. All 8 of these tables are in the same Excel file but different tables.

List of Data Tables

How can I take this data and create the matrix I linked above? I've tried creating calculated fields to count the number of active employees for each time period, but I can't then seem to get the matrix to organize itself correctly in a table.

Current Status

I feel like the easiest solution would be to query this so that I just have a snapshot of all active employees at the beginning of each month and year with month and year columns, but I'm not sure how to convert what I have now, into that sort of structure.

Thanks again.

AnilGoyal
  • 25,297
  • 4
  • 27
  • 45
mkvalvik
  • 13
  • 3
  • This seems like a data restructuring problem instead of querying. You may have to properly `JOIN` and `UNION` your data into one table before proceeding further. That will, however, depend on present structure of individual tables. – AnilGoyal Oct 30 '20 at 01:09

1 Answers1

0

I fear you have to extensively restructure your data before proceeding to build a view/crosstab, as is evident from the current status of your data (screenshot shared by you). You can do it much easily in excel. Meanwhile, I recommend/suggest you to read the paper by Hadley Wickham, renowned statistician/data scientist at this page https://vita.had.co.nz/papers/tidy-data.pdf

Still, I am trying to give you the steps which you can follow-

Step-1 Rename all columns of headcount tables by removing years from these. (Keep year names in sheets instead). This will give same column names for your all headcount tables.

Step-2 UNION all these headcount tables in data-tab of tableau. Keep sheet_names in a separate columns which will later-on be used to extract years' values.

Step-3 PIVOT all months columns to rows (In data tab only)

Step-4 Extract year names from file/sheetname column

Step-5 This will give a table structure with three useful columns to build your crosstab i.e. 1. Year (to be placed in columns); 2. Months (to be placed in rows) and 3. Headcount value (to be placed on viz/text marks card)

AnilGoyal
  • 25,297
  • 4
  • 27
  • 45
  • 1
    I think you're right here. This process was exactly what I had in mind, but wasn't sure how to execute. Thanks for the resources! – mkvalvik Oct 30 '20 at 14:48