View the original community article here
Last tested: Nov 10, 2020
When using the Excel-style value formatting in a visualization, we can create a multiple conditions by linking them with a ;. For instance, applying two or more conditions [>=300]0.00,\M; [<=299] 0.00 \K in the visualization directly.
This is possible to be able to format positive, negative, 0 values and text. From Microsoft's documentation:
The following example shows the four types of format code sections.
Format for positive numbers
Format for negative numbers
Format for zeros
Format for text
If you specify only one section of format code, the code in that section is used for all numbers. If you specify two sections of format code, the first section of code is used for positive numbers and zeros, and the second section of code is used for negative numbers. When you skip code sections in your number format, you must include a semicolon for each of the missing sections of code. You can use the ampersand (&) text operator to join, or concatenate, two values.
Workaround for this for the front end would be create a table calculation that uses if(), substring and concatenation to accomplish the formatting. From the LookML we have a lot of formatting options with the html: parameter.
An example for using HTML in our LookML could look like this:
html:
{% if {{value}} >= 0 %}
{{ rendered_value }}
{% elsif {{value}} <=-1000000 %}
-£{{ value | divided_by: 1000000 | round: 2 | times: -1 }}M
{% elsif {{value}} <=-1000 %}
-£{{ value | divided_by: 1000 | round: 2 | times: -1 }}K
{% elsif {{value}} <0 %}
-£{{ value | times: -1 }}
{% endif %}
One Example of a table calculation formatting for values in the millions to show as "0.0 M", in the thousands as "0.0 K". and similar formatting for negative millions and thousands:
if(${calculation_2}>=1000000,
concat(to_string(round(${calculation_2}/1000000,1)), " M"),
if(${calculation_2}>=1000,
concat(to_string(round(${calculation_2}/1000,1)), " K"),
if(${calculation_2}<=-1000000,
concat(to_string(round(${calculation_2}/1000000,1)), " M"),
if(${calculation_2}<=-1000, concat(to_string(round(${calculation_2}/1000,1)), " K"),
to_string(${calculation_2})
)
)
)
)
This content is subject to limited support.