tableau-desktop

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


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

  • 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