How to Group Measures and Make Columns Table Source Name

1
votes

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.

1
pivotuniontableau-apicrosstabrosters
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

1 Answers

0
votes

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)