I am trying to create a calc field where number formatting and sufficient is added based on parameter selection.
CASE [CIO Capacity Metrics Parameter]
WHEN '%' then STR(ROUND(100*(SUM([HRS_HOURS])/TOTAL(SUM([HRS_HOURS]))),0)) + '%'
WHEN 'FTE' then REGEXP_REPLACE(STR(SUM([HRS_MONTH_FTE])),"(\\d)(?=(\\d{3})+$)","\\1,")
ELSE REGEXP_REPLACE(STR(SUM([HRS_HOURS])),"(\\d)(?=(\\d{3})+$)","\\1,")
END
trying to utilize REGEXP_REPLACE in order to add in a thousands separator (number, no need for a $ prefix). This is just spitting out numbers like 9332.19134785728872411
. I know I need to round, but why am I not getting a comma as a thousands separator? Isn't that stated right here: ","\\1,")
?
EDIT:
So I tried the suggested calc, and this is what I have, it still isn't populating any commas as separators:
CASE [CIO Capacity Metrics Parameter]
WHEN '%' THEN
CASE [Incl. / Excl. OOO Parameter]
WHEN 0 THEN STR(ROUND(100*(SUM([HRS_HOURS])/TOTAL(SUM([HRS_HOURS]))),0)) + '%'
ELSE STR(ROUND(100*(SUM([Hours Total (Excl. OOO)])/TOTAL(SUM([Hours Total (Excl. OOO)]))),0)) + '%'
END
WHEN 'FTE' THEN
CASE [Incl. / Excl. OOO Parameter]
WHEN 0 THEN REGEXP_REPLACE(STR(ROUND(SUM([HRS_MONTH_FTE]),0)),"(\\d)(?=(\\d{3})+$)", "\\1," )
ELSE REGEXP_REPLACE(STR(ROUND(SUM([FTE Total (Excl. OOO)]),0)),"(\\d)(?=(\\d{3})+$)", "\\1," )
END
WHEN 'Hours' THEN
CASE [Incl. / Excl. OOO Parameter]
WHEN 0 THEN REGEXP_REPLACE(STR(ROUND(SUM([HRS_HOURS]),0)),"(\\d)(?=(\\d{3})+$)", "\\1," )
ELSE REGEXP_REPLACE(STR(ROUND(SUM([Hours Total (Excl. OOO)]),0)),"(\\d)(?=(\\d{3})+$)", "\\1," )
END
END
9332.19134785728872411
), regex (\\d)(?=(\\d{3})+$)
doesn't match as it expects the string to end after a multiple of three digits, which isn't the case when decimals are present.REGEXP_REPLACE
.CASE [CIO Capacity Metrics Parameter]
WHEN '%' THEN
STR(ROUND(100 * (SUM([HRS_HOURS]) / TOTAL(SUM([HRS_HOURS]))), 0)) + '%'
WHEN 'FTE' THEN
REGEXP_REPLACE( STR(ROUND(SUM([HRS_MONTH_FTE]), 0)), "(\\d)(?=(\\d{3})+$)", "\\1," )
ELSE
REGEXP_REPLACE( STR(ROUND(SUM([HRS_HOURS]), 0)), "(\\d)(?=(\\d{3})+$)", "\\1," )
END