Anne - please post your TWBX workbook - is your DATE field and actual date or is it a text (string field) -

Jim

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

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!

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

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

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