Greetings, programs!

Something that came up on OTN:
Cast a metric (number) as char but still have the thousand separator. E.g.: if “Sales” > 1000 then “Nothing to report” else 1’000?

Here’s a formula you can use:

case when (LENGTH(cast(Fact.Sales as char)) > 12) then
(SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) – 15 FOR 3) || ””)
else ” END ||
case when (LENGTH(cast(Fact.Sales as char)) > 9) then
SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) – 12 FOR 3) || ””
else ” END ||
case when (LENGTH(cast(Fact.Sales as char)) > 6) then
SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) – 9 FOR 3) || ””
else ” END ||
case when (LENGTH(cast(Fact.Sales as char)) > 0) then
SUBSTRING(cast(Fact.Sales as char) FROM LENGTH(cast(Fact.Sales as char)) – 6 FOR 7)
else ‘Nothing to report’ END

End of line.