Need help on table expression.

[Anonymous]

Hi All,

I have some month end data on employees, which looks like below. I need to find employees that are at or above Average Retirement age, in a given year(YTD).

Wrk_cnt = 1, if employee is active as of that month end

Retirement = 1, if employee retired that month. If they retire, there will be no row for that emplid next month.

MONTH_END_DT	EMPLID	Age as of Year end	Retirement	WRK_COUNT
1/31/2014	0001	54	0	1
2/28/2014	0001	54	0	1
3/31/2014	0001	54	0	1
4/30/2014	0001	54	0	1
5/31/2014	0001	54	0	1
6/30/2014	0001	54	0	1
7/31/2014	0001	54	0	1
8/31/2014	0001	54	0	1
9/30/2014	0001	54	0	1
10/31/2014	0001	54	0	1
11/30/2014	0001	54	0	1
12/31/2014	0001	54	0	1
1/31/2015	0001	55	0	1
2/28/2015	0001	55	0	1
3/31/2015	0001	55	0	1
4/30/2015	0001	55	0	1
5/31/2015	0001	55	0	1
6/30/2015	0001	55	0	1
7/31/2015	0001	55	0	1
8/31/2015	0001	55	0	1
9/30/2015	0001	55	0	1
10/31/2015	0001	55	0	1
11/30/2015	0001	55	0	1
12/31/2015	0001	55	0	1
1/31/2016	0001	56	0	1
2/29/2016	0001	56	0	1
3/31/2016	0001	56	0	1
4/30/2016	0001	56	0	1
5/31/2016	0001	56	0	1
6/30/2016	0001	56	0	1
7/31/2016	0001	56	0	1
8/31/2016	0001	56	0	1
9/30/2016	0001	56	0	1
10/31/2016	0001	56	0	1
11/30/2016	0001	56	0	1
12/31/2016	0001	56	0	1
1/31/2017	0001	57	1	0
1/31/2015	0002	50	0	1
2/28/2015	0002	50	0	1
3/31/2015	0002	50	0	1
4/30/2015	0002	50	0	1
5/31/2015	0002	50	0	1
6/30/2015	0002	50	0	1
7/31/2015	0002	50	0	1
8/31/2015	0002	50	0	1
9/30/2015	0002	50	0	1
10/31/2015	0002	50	0	1
11/30/2015	0002	50	0	1
12/31/2015	0002	50	0	1
1/31/2016	0002	51	0	1
2/29/2016	0002	51	0	1
3/31/2016	0002	51	0	1
4/30/2016	0002	51	0	1
5/31/2016	0002	51	0	1
6/30/2016	0002	51	0	1
7/31/2016	0002	51	0	1
8/31/2016	0002	51	0	1
9/30/2016	0002	51	0	1
10/31/2016	0002	51	0	1
11/30/2016	0002	51	0	1
12/31/2016	0002	51	0	1

Avg Age at retirement =Sum(if((Year([MONTH_END_DT])=Year([InputDateVar])or Year([MONTH_END_DT])=Year(YearVar1) or Year([MONTH_END_DT])=Year(YearVar2) or Year([MONTH_END_DT])=Year(YearVar3) or Year([MONTH_END_DT])=Year(YearVar4) or Year([MONTH_END_DT])=Year(YearVar5) or Year([MONTH_END_DT])=Year(YearVar6) or Year([MONTH_END_DT])=Year(YearVar7)) and [Retirement ]=1,[EMPL_AGE],0))/
Sum(if((Year([MONTH_END_DT])=Year([InputDateVar]) or Year([MONTH_END_DT])=Year(YearVar1) or Year([MONTH_END_DT])=Year(YearVar2) or Year([MONTH_END_DT])=Year(YearVar3) or Year([MONTH_END_DT])=Year(YearVar4) or Year([MONTH_END_DT])=Year(YearVar5) or Year([MONTH_END_DT])=Year(YearVar6) or Year([MONTH_END_DT])=Year(YearVar7)),[Retirement ],0))

InputDateVar = MONTH_END_DT selected from Input box. Input box contains all distinct values of month end dt present in data.

YearVar1 = AddMonths(InputDateVar,-Month(InputDateVar),1)

YearVar2 = =AddMonths(YearVar1,-12,1) and so on...

At or above average age of retirement = Sum(WRK_COUNT) , for age is greater than or equal to Avg age of retirement. I want a table like below. But not able to calculate rightmost column. Could someone please help.

YEAR	Headcount	Retirement Rate	Average Age at Retirement	At or above Average age at retirement
2011	32000	2.17%	59.60
2012	31000	2.07%	60.04
2013	31200	1.97%	59.27
2014	32003	2.27%	59.07
2015	32050	2.20%	60.06

[Anonymous]

Hi Ekta Rathi,

Could you please try the below logic with set analysis

sum({$<WRK_COUNT={1}>,Mon_END_DT}

OR (E{$<Retirement={1}>,EMPLID}))

Thanks Regards,

Ramesh.P