Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have a table that for each month shows what the ending of the month headcount is. It uses this expression:
Month Headcount = Sum( {<IsLastDay={"Y"}>} Active)
In order to do a calculation (turnover) I need to also get the first day of the year headcount and use that number in each row of the table.
I can get the first day of the month, for the first month number with this expression:
First Day of Year =Sum( {<IsFirstDay={"Y"}, Month = {"1"}>} Active)
My question is: How can I repeat that first day of the month in the table or get that result in each row's calculation. The final result would be using this expression: =YTD Terminations / (First Day of Year + Month Headcount)/2
Where I am stuck is that it only shows up in the first month. Here's what the table looks like now:
Year | Month | Headcount | Terminations | First Day of Year |
2015 | 1 | 5000 | 635 | 4950 |
2015 | 2 | 5084 | 457 | |
2015 | 3 | 5187 | 565 | |
2015 | 4 | 4922 | 240 | |
2015 | 5 | 5352 | 502 | |
2015 | 6 | 5250 | 680 | |
2015 | 7 | 5270 | 275 | |
2015 | 8 | 5218 | 196 | |
2015 | 9 | 4800 | 544 | |
2015 | 10 | 5875 | 646 | |
2015 | 11 | 5466 | 420 | |
2015 | 12 | 4927 | 179 | |
2016 | 1 | 5855 | 360 | 5560 |
2016 | 2 | 5887 | 310 | |
2016 | 3 | 5464 | 420 | |
2016 | 4 | 5984 | 433 | |
2016 | 5 | 5464 | 356 | |
2016 | 6 | 5123 | 411 | |
2016 | 7 | 5649 | 345 | |
2016 | 8 | 5345 | 353 | |
2016 | 9 | 5935 | 304 | |
2016 | 10 | 5140 | 313 | |
2016 | 11 | 5456 | 383 | |
2016 | 12 | 5949 | 327 |
I want it to look like is this:
Year | Month | Headcount | Terminations | First Day of Year |
2015 | 1 | 5000 | 635 | 4950 |
2015 | 2 | 5084 | 457 | 4950 |
2015 | 3 | 5187 | 565 | 4950 |
2015 | 4 | 4922 | 240 | 4950 |
2015 | 5 | 5352 | 502 | 4950 |
2015 | 6 | 5250 | 680 | 4950 |
2015 | 7 | 5270 | 275 | 4950 |
2015 | 8 | 5218 | 196 | 4950 |
2015 | 9 | 4800 | 544 | 4950 |
2015 | 10 | 5875 | 646 | 4950 |
2015 | 11 | 5466 | 420 | 4950 |
2015 | 12 | 4927 | 179 | 4950 |
2016 | 1 | 5855 | 360 | 5560 |
2016 | 2 | 5887 | 310 | 5560 |
2016 | 3 | 5464 | 420 | 5560 |
2016 | 4 | 5984 | 433 | 5560 |
2016 | 5 | 5464 | 356 | 5560 |
2016 | 6 | 5123 | 411 | 5560 |
2016 | 7 | 5649 | 345 | 5560 |
2016 | 8 | 5345 | 353 | 5560 |
2016 | 9 | 5935 | 304 | 5560 |
2016 | 10 | 5140 | 313 | 5560 |
2016 | 11 | 5456 | 383 | 5560 |
2016 | 12 | 5949 | 327 | 5560 |
It doesn't have to actually look like that, I just need to get the First Day of Year number. That is so, as I said above, I can use this calculation:
=YTD Terminations / (First Day of Year + Month Headcount)/2
So the end result would be this:
Year | Month | Headcount | Terminations | First Day of Year | Turnover |
2015 | 1 | 5000 | 635 | 4950 | 3.2% |
2015 | 2 | 5084 | 457 | 4950 | 5.4% |
2015 | 3 | 5187 | 565 | 4950 | 8.2% |
2015 | 4 | 4922 | 240 | 4950 | 9.6% |
2015 | 5 | 5352 | 502 | 4950 | 11.6% |
2015 | 6 | 5250 | 680 | 4950 | 15.1% |
2015 | 7 | 5270 | 275 | 4950 | 16.4% |
2015 | 8 | 5218 | 196 | 4950 | 17.5% |
2015 | 9 | 4800 | 544 | 4950 | 21.0% |
2015 | 10 | 5875 | 646 | 4950 | 21.9% |
2015 | 11 | 5466 | 420 | 4950 | 24.8% |
2015 | 12 | 4927 | 179 | 4950 | 27.0% |
2016 | 1 | 5855 | 360 | 5560 | 1.6% |
2016 | 2 | 5887 | 310 | 5560 | 2.9% |
2016 | 3 | 5464 | 420 | 5560 | 4.9% |
2016 | 4 | 5984 | 433 | 5560 | 6.6% |
2016 | 5 | 5464 | 356 | 5560 | 8.5% |
2016 | 6 | 5123 | 411 | 5560 | 10.7% |
2016 | 7 | 5649 | 345 | 5560 | 11.8% |
2016 | 8 | 5345 | 353 | 5560 | 13.7% |
2016 | 9 | 5935 | 304 | 5560 | 14.3% |
2016 | 10 | 5140 | 313 | 5560 | 16.8% |
2016 | 11 | 5456 | 383 | 5560 | 18.1% |
2016 | 12 | 5949 | 327 | 5560 | 18.7% |
I had tried some different approaches such as using variables and trying out using an equal sign in the expression, and sometime not. I'm still a bit fuzzy on when the expression calculates based on if there is an equal sign or not in the expression.
Thank you for the help!