Avg Age by FY if BusGroup=HQPEO

[jelena_m24]

Hi. All I need is to get Avg. Age for Business Group HQ/PEO by fiscal year (FY).

I tried a couple of different approaches:
1)
average([AGE] for [FY],[BUSINESS_GROUP_NAME]='HQ/PEO')

2)
CASE [BUSINESS_GROUP_NAME] WHEN 'HQ/PEO' THEN
(
total(([EMPLOYEE_CT] *[AGE]) for [FY],[BUSINESS_GROUP_NAME]='HQ/PEO') /
total([EMPLOYEE_CT] for [FY], [BUSINESS_GROUP_NAME]='HQ/PEO')
)
END

in both instances, I'm getting 4 values: 2*2 (2 for the two selected fiscal years that I'm looking at, 2016 and 2017, and value for HQ/PEO 46.79 and 47.21 that I need but also the 2 values that are picking the average of every other bus. group for 2016 and 2017.

Question: how can I get only 2 values displayed, for HQ/PEO for 2016 and 2017?

[Lynn]

Hi. All I need is to get Avg. Age for Business Group HQ/PEO by fiscal year (FY).

I tried a couple of different approaches:
1)
average([AGE] for [FY],[BUSINESS_GROUP_NAME]='HQ/PEO')

2)
CASE [BUSINESS_GROUP_NAME] WHEN 'HQ/PEO' THEN
(
total(([EMPLOYEE_CT] *[AGE]) for [FY],[BUSINESS_GROUP_NAME]='HQ/PEO') /
total([EMPLOYEE_CT] for [FY], [BUSINESS_GROUP_NAME]='HQ/PEO')
)
END

in both instances, I'm getting 4 values: 2*2 (2 for the two selected fiscal years that I'm looking at, 2016 and 2017, and value for HQ/PEO 46.79 and 47.21 that I need but also the 2 values that are picking the average of every other bus. group for 2016 and 2017.

Question: how can I get only 2 values displayed, for HQ/PEO for 2016 and 2017?

You can try something like below. The null values will be ignored so only the HQ/PEO ages are included in the average.

You might still need the "for" to define the scope. If so, it would go after the case statement.

Code Select Expand


average ( case when [BUSINESS_GROUP_NAME]='HQ/PEO' then [AGE] else null end )


*edit: corrected mistake in expression*

[jelena_m24]

Hi. Thanks much - it worked when I used the formula below.

average (IF ([BUSINESS_GROUP_NAME]='HQ/PEO') then ([AGE]) else null for [FY], [BUSINESS_GROUP_NAME]