How do I concatenate 2 dollar columns and preserve dollar formatting.

[CLO]

Hello. Lets say I have 2 columns with the following dollar amounts - 

$250,294 and $717,543

How do I concatenate both in a 3rd column to get the following, while preserving the dollar formatting. See my desired output below - 

“$250,294 out of $717,543”

When I use the “concat()” function, I loose the dollar formatting.

[Dawid]

You’r elosing it because the dollar sign is added at rendering/visualisation stage of the data. You may be able to do something when adding the dollar signs to your concat function:

concat(“$”, ${first_field}, “ out of $”, ${second_field})

[CLO]

@Dawid Thanks. I tried that and it works, however you don’t get the comma separated format.

[Dawid · Accepted solution]

Correct, unfortunately creating a string field in Table Calculation has its downsides. You’re creating a new field which means no formatting is applied because of the order of execution of different aspects of the data. 

You could also create this field in LookML (though it would be duplicated). You wouldn’t concat this field in SQL just use first field in SQL but then use {{ second_field._value}} in the html parameter. I recall some ways to apply at least the thousand delimiter in there. 

It would be something close to this: 

measure: first_field {
  type: sum
  sql: ${TABLE}.first_field ;;
  html: {{ rendered_value }} out of {{ second_field._rendered_value }} ;;
  value_format_name: usd
}

dimension: second_field {
  type: sum
  sql: ${TABLE}.second_field ;;
  value_format_name: usd
}

By using {{ rendered_field }}, you’re telling Looker to use the output of the rendering process of each field before combining everything into the output of HTML parameter.