-
1. Re: Calculate evolution between two years
Jim DehnerJul 17, 2017 5:38 AM (in response to Anne Renaudeau)
Anne - please post your TWBX workbook - is your DATE field and actual date or is it a text (string field) -
Jim
-
2. Re: Calculate evolution between two years
Anne Renaudeau Jul 17, 2017 6:43 AM (in response to Jim Dehner)I don't know how I can post a TWBX workbook.
My date are in actual date.
With anonymized data I have this :
For the HC, I have the HC of 12-2016 and 12-2017, it's what I want. (With the calculation : if month([Date]) = 12 then [Headcount] end)
But for the leavers I have only between 01-2017 and 12-2017. I would like to have between 12-2016 and 12-2017.
And without that I can't do the calculation of the turnover : Leaver / (( HC of 12-2016 + HC of 12-2017) / 2 ).
-
3. Re: Calculate evolution between two years
Jim DehnerJul 17, 2017 6:23 AM (in response to Anne Renaudeau)
Anne
You can attache your TWBX workbook by first selecting the "Advanced Editor and add an attachment " in the upper right corner of the frame
The box will change then you select "attach" in the lower right corner of the frame if will open a search window and you can select the file to attach
Jim
-
4. Re: Calculate evolution between two years
Marc-Anthony Di Biase Jul 17, 2017 6:25 AM (in response to Anne Renaudeau)Hi Anne Renaudeau,
Assuming your date field is an actual date field, you would be able to retrieve the desired results with a simple IF statement.
IF(year([DATE]) = year(today())-1)) THEN [Number of employees] END
This formula is effectively saying if the year of the record is equal to today's year (2017) - 1 year, (2016) then output the number of employees.
You would then be able to use this number, in conjunction with 2017's employee count to get your turnover rate. If you're using a moving target (i.e. you would want to see 2015, 2014 etc. turnover rates as well) this would get a little bit more complex, and I would recommend using a FIXED calculation.
Let me know if this helps and your use case!
-
5. Re: Calculate evolution between two years
Anne Renaudeau Jul 17, 2017 6:43 AM (in response to Jim Dehner)Jim Dehner, thanks
-
tbl.twb 11.7 KB
-
-
6. Re: Calculate evolution between two years
Anne Renaudeau Jul 17, 2017 6:43 AM (in response to Marc-Anthony Di Biase)Marc-Anthony Di Biase, yes it's what I have tried to do. As you said, if I want a moving target (want I will want at the end)... it's more complex. Thanks for your help, maybe no solutions to my problem ^^
-
7. Re: Calculate evolution between two years
Jim DehnerJul 17, 2017 7:29 AM (in response to Anne Renaudeau)
Sorry Anne - the file came across but the data did not - lets try it again
Go to File - EXPORT PACKAGED WORKBOOK -
also I am a little confused on the data Leaver - what does that data represent
Thanks
Jim
Hang in there - we'll get you a result
-
8. Re: Calculate evolution between two years
Anne Renaudeau Jul 17, 2017 7:58 AM (in response to Jim Dehner)Each line is one employee, if Leaver = 1 then the employee leave the company at this date.
-
tbl.twbx 17.1 KB
-
-
9. Re: Calculate evolution between two years
Jim DehnerJul 17, 2017 8:30 AM (in response to Anne Renaudeau)
OK Anne - here is a solution for the change in the 12-2016 to 12 2017 time frame
I broke the calculations into pieces - they could be combined
First is the fixed number of leavers by year = { FIXED year([Date]):sum([Leaver])}
Next is the average of the year end headcounts as (sum([HC - december])+LOOKUP(sum([HC - december]),-1))/2 (I think that is what you wanted
Then the final is just dividing one by the other = sum([fixed leavers by year])/([Average Of HC at 12th month])
You end up with something like this
Now you said that you wanted to go back further but don't have the leaver data - The only thing I could suggest is for the first year you could get a net change number my looking month to month at the HC difference and then taking the difference between sum of the monthly HC changes - and the 2016 beginning and end year head counts - the first calculation will give you the net +/- each month the last will give you the difeerenc over the year - daking the difference should net out the hires?
Let me know if this helped
Jim
-
tbl with solution.twbx 20.2 KB
-
-
10. Re: Calculate evolution between two years
Jim DehnerJul 17, 2017 9:37 AM (in response to Jim Dehner)
Hi Anne
I thought more about the way to get the 2016 leaver data
- you would need to your at the lowest level you have data (like an individual location) and then sum the absolute value of the end of month change for each month in 2016 and then net out that total change from the first of the year to end- you will not get the intra-month change right - that is if you had a hire and someone leave in the same month you would not pick it up - you will be closer but I don't know of another way to pickup the missing data
Jim
-
11. Re: Calculate evolution between two years
Anne Renaudeau Jul 18, 2017 12:20 AM (in response to Jim Dehner)Thanks Jim, I will try this today !