Help with understanding set analysis and using the result in a table

treborscottnam — Tue, 08 Jun 2021 17:01:11 GMT

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!

topic Help with understanding set analysis and using the result in a table in QlikView

Help with understanding set analysis and using the result in a table