Formatting Dollars in string using Table Calculation

[mprogano]

Here’s a fun little hack — I wanted to create a written text statement that can be copy and pasted out of Looker. One issue with that is that money isn’t formatted so $310,000 would come up as 310000… as you can imagine that gets to be a LOT to read as the numbers change and grow.

So I decided to attack this with table calculation BEFORE the final table calculation that I’m using to write the copy/paste statement.

At the highest level, I am concatenating a bunch of ifs. The if statements check for at each digit if it exists using a length (greater than equal to the position number). If the digit does exist, then it prints it. Additionally, it adds a comma if the digit after the comma exists. So you’ll notice a slight difference in the code on the 10th, 7th, and 4th digits.

It’s worth noting that this assumes there is NO “cents” needed and rounds so that isn’t an issue. This also only supports numbers between 1 and 10 digits long, though it’s easy to extend.

Also, I tried writing this using sets of 3 originally, but when you start a substring longer than the number is, it ignores the extra non-existing digits and starts you at the nearest neighbor — I had duplicate numbers without realizing it.

concat(
  "$",
  if(
    length(to_string(round(${some_dollar_amount},0))) >= 10,
    concat(
      substring(to_string(round(${some_dollar_amount},0)), -10, 1),
      ","
      #The above adds a comma after 10th digit if it exists
    ),
    ""
  ),
  if(
    length(to_string(round(${some_dollar_amount},0))) >= 9,
    substring(to_string(round(${some_dollar_amount},0)), -9, 1),
    ""
  ),
  if(
    length(to_string(round(${some_dollar_amount},0))) >= 8,
    substring(to_string(round(${some_dollar_amount},0)), -8, 1),
    ""
  ),
  if(
    length(to_string(round(${some_dollar_amount},0))) >= 7,
    concat(
      substring(to_string(round(${some_dollar_amount},0)), -7, 1),
      ","
      #The above adds a comma after 7th digit if it exists
    ),
    ""
  ),
  if(
    length(to_string(round(${some_dollar_amount},0))) >= 6,
    substring(to_string(round(${some_dollar_amount},0)), -6, 1),
    ""
  ),
  if(
    length(to_string(round(${some_dollar_amount},0))) >= 5,
    substring(to_string(round(${some_dollar_amount},0)), -5, 1),
    ""
  ),
  if(
    length(to_string(round(${some_dollar_amount},0))) >= 4,
    concat(
      substring(to_string(round(${some_dollar_amount},0)), -4, 1),
      ","
      #The above adds a comma after 4th digit if it exists
    ),
    ""
  ),
  if(
    length(to_string(round(${some_dollar_amount},0))) >= 3,
    substring(to_string(round(${some_dollar_amount},0)), -3, 1),
    ""
  ),
  if(
    length(to_string(round(${some_dollar_amount},0))) >= 2,
    substring(to_string(round(${some_dollar_amount},0)), -2, 1),
    ""
  ),
  if(
    length(to_string(round(${some_dollar_amount},0))) >= 1,
    substring(to_string(round(${some_dollar_amount},0)), -1, 1),
    ""
  )
)