11 Replies Latest reply on Jul 18, 2017 12:20 AM by Anne Renaudeau

# Calculate evolution between two years

Hi !

I have data like that :

DATE Number of employees
12-20161048
01-2017

1050

02-20171024
03-20171036
04-20171054
05-20171054
06-20171035
07-20171056
08-20171058
09-20171024
10-20171058
11-20171056
12-20171055

I would like to see the turnover beetween :

- 12-2016 and 12-2017

To have :

2016-122017-12
Number of employee10481055
Turnover-leaver / ((1048 + 1055)/2)

How can I recover the data of the last year in the calculation ?

Thanks,

Anne

• ###### 1. Re: Calculate evolution between two years

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

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

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

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

Jim Dehner, thanks

• ###### 6. Re: Calculate evolution between two years

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

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

Jim Dehner

Each line is one employee, if Leaver = 1 then the employee leave the company at this date.

• ###### 9. Re: Calculate evolution between two years

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

1 of 1 people found this helpful
• ###### 10. Re: Calculate evolution between two years

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

Thanks Jim, I will try this today !