1 of 1 people found this helpful
There is a couple of complex issues with this kind of problem.
Most of the measures you need can be computed at the row level.
For example New Hire could be
ZN( if CONTAINS([Movement],'New') then [Arrival] else 0 end )
So if the Movement value contains the String New return the Arrival value. (ZN is ZeroNull or ifnull then 0, to display 0 when you have a null instead of an empty string)
EOE could look like
ZN( if CONTAINS([Movement],'EOE') or CONTAINS([Movement],'End') then -[Depart] end )
note the minus Depart, since Depart already have negative values.
For the active employee, I guess you want to remove the Internal movement , so the row logic would look like
if not CONTAINS([Movement], 'Internal') then [Arrival]+[Depart] end
Which brings to your main question, how to get the value at a specific date.
Since this number depends on the result of this calculation for the previous rows, you will need a table calculation
RUNNING_SUM( SUM( if not CONTAINS([Movement], 'Internal') then [Arrival]+[Depart] end) )
And since this is a table calculation , you need to set the computing. Right-click the triangle on the green pill and set the following
You can see the result on Test1(mc) Sheet 1(3).
Now if you want to filter the Date to keep only 2 quarters in the view, you will have a problem because the runningSum will compute using only those 2 quarters, so all activeEmployees would start at 0 in 2018 Q4.
One way to trick Tableau to compute a table calculation using all the data in the context and not only the data in the view is to make the date filter a table calculation.
This have the side effect of changing the order of operation, and all table calculations will compute before the filtering is applied.
The following table calculation will simply lookup the date with an index of 0 , so the date itself in the format yyyy Qx
LOOKUP( MIN( DATENAME('year', [First/last Day] ) + ' Q' + DATENAME('quarter', [First/last Day] ) ),0 )
You can see the result on Sheet1(4).
But you have another problem. You don't see all Dept for Q4. As you can see on the bottom left you have 15 marks. Each mark represent a number of rows, and Tableau compute each calculation for each mark. But since you don't have any data for Admin in Q4 , you don't have any mark for that.
If you look in the Excel attached, it's a copy of the previous one, but I added a couple of rows in order to have one date for each Quarter for each Dept.
And if you look on Test1(mc2), I switched the datasource for this file and I now have 24 marks and one row for every Dept in Q4.
So you will need to do some padding at the source to have a mark for every Quarter for every Dept.
Thank you so much for your help! Really well explained, I've tried some formulas already and it worked great.
I'll work on my original data set to had my missing marks and I should be good .
Thanks again! My managers will be really happy to have those data!
Glad I could help.
Bonne journée !