CODEWITHSUNDEEP
tableau-api
SASUSMC
SASUSMC

Reputation: 681

Commas in String numbers in Tableau

I am using a number inside a calculation and have to turn it into a string. When I do this, I lose the comma formatting. Using another post:

Changing Number Format in a String

However the formula, I am using:

STR(ROUND(LOOKUP(sum([Actual]),0),0))

Does not seem to be working. The full calculation is here:

If attr([Kpi Nm]) = "Policy Retention Better-than-State Average"
    Then str(round(sum([Actual]) * 100, 2)) + "%"
ElseIf attr([Kpi Nm]) = "Policy Retention Improvement (or > 90%)**"
    Then str(round(sum([Actual]) * 100, 2)) + "%"
Elseif attr([Kpi Nm]) = "Premium Growth"
    Then str(round(sum([Actual]) *100, 2)) + "%"
Elseif attr([Kpi Nm]) = "PIF Growth"
    Then str(round(sum([Actual]), 2))
Elseif attr([Kpi Nm]) = "Product Density"
    Then "NA"
else
   STR(ROUND(LOOKUP(sum([Actual]),0),0))
End

I need to keep the commas on the Actual for the else statement. Any help provided would be much appreciated. The Lookup is supposedly a trick to make it work but does not work in my case.

Thanks,

Upvotes: 4

Views: 5827

Answers (2)

Robert R.
Robert R.

Reputation: 41

This should work to keep the commas on the Actual else statement. I had a similar issue and combined a few found answers and this worked for me.

REGEXP_REPLACE(STR(SUM([Actual])),"(\d)(?=(\d{3})+$)","$0,")

Upvotes: 4

Sam M
Sam M

Reputation: 4166

I found this regular expression on another site and haven't personally tried it. Not sure if Tableau's regex implementation supports this particular type of expression though because it needs to look ahead. Use it with the regular expression replace function.

(\d)(?=(\d{3})+$)

Upvotes: 2

Related Questions