Value format in looker. We are trying to break number in billiion, million and thousands dynamically

[akhilsai]

Folks,

We are trying to format numbers in Looker dynamically in billions, million, and thousands dynamically. Below we tried in millions and thousands. It working as expected. But we are not able to figure out negative numbers. Appreciate it if we get any help.

named_value_format: millions {
value_format: "[>=1000000]0.0,,\" M\";[>=1000]0.000,\" K\";0"
}

[Saty]

To Google Looker Folks
Need Value format for negative numbers.
I was trying this :  value_format: "[>=1000000]0.0,,\"M\";[>=1000]0.0,\"K\";[>=0]0;[<=-1000000]-0.0,,\"M\";[<=-1000]-0.0,\"K\";-0" but it was not working. Any Insights on this?  Appreciate it if we get any help.

[Mohammad_Ali]

In manifest you can define the formatting and then you can apply in the dimension/measure.

[Saty]

constant: negative_format {
  value: "{% if value < 0 %}<p style='color:red;'>({{rendered_value}})</p>{% else %} {{rendered_value}} {% endif %}"
}

[Saty]

html:
{% if value >= 1000000 and value < 1000000000 %}
${{value | divided_by: 1000000 | round:1 }}M
{% elsif value >= 1000 and value < 1000000 %}
${{value | divided_by: 1000 | round:1 }}K
{% elsif value >= 0 and value < 1000 %}
${{value | round:0 }}
{% elsif value > -1000 and value < 0 %}
${{value | round:0 }}
{% elsif value > -1000000 and value <= -1000 %}
${{value | divided_by: 1000 | round:1 }}k
{% elsif value > -1000000000 and value <= -1000000 %}
${{value | divided_by: 1000000 | round:1 }}M
{% else %}
'fail'
{% endif %} ;;
}

[shakermakerk]

I just want to go on the record to say it REALLY annoys me that that we have to write code for basic functions like this. Other things that annoy me about looker:

• i cant define the metrics in the tooltip without writing weird json chart config code
• i can't dynamically change the time period without messing around creating parameters and using liquid
• period-on-period comparisons are poor

[robertcarr]

This article should help answer your question: https://www.googlecloudcommunity.com/gc/Technical-Tips-Tricks/More-than-three-conditional-dynamic-va...

The value_format only allows for 3 conditions, to use more than 3 conditions, you need to use HTML.

[robertcarr]

@shakermakerk - I'm sorry about your frustrations, you should engage with your Looker rep so they can better understand them and raise relevant Feature Requests if needs be.

At a high level, the custom viz editor does allow for you to edit the tooltip in JSON, that is correct. We also plans for an AI assistant to help with this. You can also use HTML: https://cloud.google.com/looker/docs/best-practices/cookbook-visualizations-tooltip-customization

Liquid is a really powerful tool for SQL injection and in this scenario is how we input different timeframes, as you say. Another option is using native drills. If you prefer this to happen in a different way, you can always raise a FR.

We have extensive options for period comparisons, something that can be quite tricky with SQL (https://www.googlecloudcommunity.com/gc/Technical-Tips-Tricks/Methods-for-Period-Over-Period-PoP-Ana...). You can also do this with native charts i.e. pivot by time period or use table calcs for more niche comparisons.