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

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

• tableau-desktop

Share Improve this question Follow edited Nov 18, 2024 at 18:31 NidenK asked Nov 18, 2024 at 18:15 NidenKNidenK 35911 silver badge1111 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

• 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