Help with understanding set analysis and using the result in a table
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.