-
1. Re: How to create a table calculation to understand the evolution of a workforce in a career?
Ok. I am sending now Tableau's workbook. I converted the years (YYYY) into dates.
-
2. Re: How to create a table calculation to understand the evolution of a workforce in a career?
Hi Rogerio,
I took a look at the data you sent over. I'd recommend reformatting it. Ideally, it would have column headers as follows date, level (first promotion, second promotion, etc) and number of employees in each level by date. I created a view based off of your data. It should be noted that the count of employees in each year may be misrepresented if an employee is present in multiple levels in a single year. With that said, I've attached what I came up given the data.
Hope this helps!
Paul
interworks
-
3. Re: How to create a table calculation to understand the evolution of a workforce in a career?
Hi Paul. Thank you very much for your effort. I really need to updated table. But my problem is another one.
How may I tableau understand that two number are connected. Therefore if one employee stayed in one position from 1973 to 1979 [his next promotion], this means it was at that position in 1973, 1974, 1975, 1978 and 1979. In the graph you plotted it would appear only 1973.
I also want to count how many employees I had in a specific year. I found a similar discussion here (How to count ID #'s within a date range by date?), but could not find a proper answer.
-
4. Re: How to create a table calculation to understand the evolution of a workforce in a career?
Hey Rogerio,
I'd reformat the data as pictured below. I also attached a wb with a simple way to view it and another view with the dates pivoted. Hope it helps!
-
5. Re: How to create a table calculation to understand the evolution of a workforce in a career?
Sorry for the delay. I have just converted the data as you told me to do and adapted the terminology.
The career works like this: 1) Admission; 2) Second Secretary; 3) First Secretary; 4) Councellor; 5) Second Class Minister; and 6) First Class Minister. So after the year of admission the employee stays in that situation until he becomes a Second Secretary or he is Retired -- in this last case, it means in the following year the employee should not be in the sample. Some employees are promoted until step 6, but they are a minority.
What I want to show is use the year as my horizontal axis and in the vertical axis I want to know the number of employees in each category.
-
6. Re: How to create a table calculation to understand the evolution of a workforce in a career?
Anyone? Still need help?
-
7. Re: How to create a table calculation to understand the evolution of a workforce in a career?
Rogerio,
I shifted some things around in your dataset so that I could use
kettan 's The Cross Join Collection Section #1 Periods Intersecting Calendar Periods
The xls file is attached and the Custom SQL Query was
SELECT *
FROM [data$] d, [lookup$] l
WHERE l.[year] BETWEEN d.[StartYear] and d.[EndYear]
I may not have done all my shifts correctly in the dataset,
but I hope that the attached could be a starting point for you.
I didn't double check the counts.
I also see that in the right side of the graph, it is not accounting for people
who are at Admission and haven't yet moved to the next level.
If someone had an Admission, but no other level, I left their EndYear as the Admission Year.
You can correct that by finding those that are still currently employed and
are at the Admission stage, and then changing their EndYear to something like 2015.
-
8. Re: How to create a table calculation to understand the evolution of a workforce in a career?
Rogerio,
On another note, your data would work very well as a Jump Plot:
Particularly, you could show how long people remained at a particular level.
And it would nicely show skips, if that would be pertinent.
It takes some more adjusments to your dataset.
I'm working through that.
-
9. Re: How to create a table calculation to understand the evolution of a workforce in a career?
Here is another take.
As others have noted, at times the easiest thing is to wrangle the data outside tableau. In this case I used Alteryx. Workflow snapshot attached.
Where the data had multiple records for the same Employee ID, I have picked the one which has the most date information available.
-
10. Re: How to create a table calculation to understand the evolution of a workforce in a career?
Thank you very much for all of you. This was really helpful. You are the true masters of Tableau! I have been trying to plot those graphs for months.
My only comments are: 1) Why Tableau does not provide a way to easily do this kind of visualization? 2) I use on MAC, so the SQL connection is a real problem. I had to install Windows 8 in order to see the amazing work you did.
Again, thank you very much.
I will try to do the Jump Plot now.
