Home Unable to utilize REGEXP correctly to get thousands separator in str calc field

Questions

Unable to utilize REGEXP correctly to get thousands separator in str calc field

November 18, 2024

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

>Solution :

When a number includes decimal places (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.
You should round to eliminate the decimal places before applying the 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