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?
Quote from: jelena_m24 on 27 Apr 2017 02:57:10 PM
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.
average ( case when [BUSINESS_GROUP_NAME]='HQ/PEO' then [AGE] else null end )
*edit: corrected mistake in expression*
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]