Dynamic value format name

[Dawid]

I sent it to Looker support as well but I was wondering if anybody has similar requirement.

I have 30+ measures that come from an aggregation table in an original currency. Instead of creating two extra fields per measure with suffixes _eur and _usd I decided to do the following.

Create parameter to allow people to choose whether they want to see converted values:

parameter: conversion_currency {
    type: unquoted
    default_value: "1"
    allowed_value: {
      label: "Euro"
      value: "EUR"
    }
    allowed_value: {
      label: "US Dollars"
      value: "USD"
    }
  }

And then based on this parameter I select a field from the table. Each row has both FX rates already available.

dimension:fx_rate {
    sql:  {% if conversion_currency._parameter_value == "EUR" %}
            ${TABLE}.fx_rate_eur
          {% elsif conversion_currency._parameter_value == "USD" %}
            ${TABLE}.fx_rate_usd
          {% else %}
            1
          {% endif %}  ;;
  }

Here is a sample measure:

measure: net_value {
    type: sum
    sql: ${TABLE}.net_value / ${fx_rate} ;;
    value_format_name: decimal_2
  }

When no currency conversion is selected it’s defaulting to net value / 1, which is great. The problem I’m having is to apply specific formatting depending on the parameter.

As far as I know I can’t use parameters in definition of my own format but it’s basically what would solve the problem

named_value_format: currency_dynamic {
  value_format: {% if conversion_currency._parameter_value == "EUR" %}
                  "eur_2"
                {% elsif conversion_currency._parameter_value == "USD" %}
                  "usd_2"
                {% else %}
                  "decimal_2"
                {% endif %}
}

and then I could use

measure: net_value {
  value_format_name: currency_dynamic
}

Which means if I had to add new currency conversion, I need to only change it in 3 places and all measures would work automatically. Any ideas how to achieve right now?

The only thing I saw as possible solution was:

html:  
          {% if conversion_currency._parameter_value == "EUR" %}
            €{{ rendered_value }}
          {% elsif conversion_currency._parameter_value == "USD" %}
            ${{ rendered_value }}
          {% else %}
            {{ rendered_value }}
          {% endif %};;

But I can’t remember where but I think I saw somebody having pointed out some shortcomings with this approach. Also this is a lot of code to repeat , not sure if I can put this into another parameter or a constant but then again constant can’t be defined in a view file

[moebe]

Hi Dawid
the disadvantage I know is that by using HTML Liquid you deprive the client of any possibility to change the formatting.
So if you have a figure that can go from say 1 Euro to 10.000.000 Euro and you make a highly aggregated report, the figures will look like this:
€ 9.897.237,27
This is not easy to read. Normally you would manually change the formatting in this report to e.g.: 0.0, digits around the number
9.9.
Unfortunately this is no longer possible with the use of HTML in the formatting.

[Dawid]

If we could do this:

value_format: "@format_constant"

That would be awesome and I think it would still allow us to override it in the visualisation.

Also my initial idea with the constant wouldn’t work because we can’t use constants in html parameter.

All I need is a variable in value_format_name 😕 So simple, yet so out of reach!

[sinatalebian]

any update on this thread? I’m really curious to see if there’s a way to make this Lookml work. I have a similar situation in which I’m trying to indicate the format based on a parameter value.  

[Dawid]

Unfortunately the only way to achieve this is still to copy the code in the HTML paramter : (

[mkull-163770151]

Just bumping this.  Came here with the exact same issue (currency conversion and dynamic display of currency type).  Would be fantastic to be able to inject a variable into value_format_name (and :label)!

[HaGi1]

If we could do this:

value_format: "@format_constant"

That would be awesome and I think it would still allow us to override it in the visualisation.

Also my initial idea with the constant wouldn’t work because we can’t use constants in html parameter.

All I need is a variable in value_format_name So simple, yet so out of reach!

You can declare a constant with Liquid formatting and call it in a measure using html tag.
Example:
 

constant: currency_value_format_liquid {
  value: "
  {% if value >=1000000000 %}

  ${{ value | divided_by: 1000000000 | round: 2 | times: 1 }} B

  {% elsif value <=-1000000000 %}

  -${{ value | divided_by: 1000000000 | round: 2 | times: -1 }} B

  {% elsif value >=1000000 %}

  ${{ value | divided_by: 1000000 | round: 2 | times: 1 }} M

  {% 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 >=1000 %}

  ${{ value | divided_by: 1000 | round: 2 | times: 1 }} K

  {% elsif value <0 %}

  -${{ value | divided_by: 1 | round: 0 | times: -1 }}

  {% elsif value >0 %}

  ${{ value | divided_by: 1 | round: 0 | times: 1 }}

  {% endif %}"

}


 

  measure: m1 {
    type: number
    sql: ${some_name} ;;
    html:
    <p >@{currency_value_format_liquid}</p>  ;;

    }

[Philipp_Hagmann]

Bumping this. Strongly.

A big disadvantage of using the html approach is, that it won’t work for visualisations. This might seem cosmetic, but Looker Team, please don’t underestimate the effects small things like this have for Enterprise customers.

I think it’s pretty bad judgement on the business side to not prioritise this issue.

[Vitaliy_Skachko]

Same issue, 
I want to have dynamic measure where users can choose between whole numbers (money) and percentage (share). SO html is not working in visuals, only in table. 

It’s 2023, but not 2013. I still wonder why this simple thing is not there.

[clayvts]

Can we get an update from Looker on this? Three years later and no response from the team. We need to be able to make value format names dynamic.

[seantabor-16794]

+1
Please add this functionality.

[jenn_calland]

+1 Same please.

[ChinmayW]

+1. Looking for some solution other than HTML 

[imoskvin]

+1. We are also running into this issue.

We need the ability to conditionally format a KPI metric measure. Depending on which KPI metric the user selects, we need to format it as dollar amounts, percentages, or an undecorated decimal numbers. We need to display this measure in heatmaps (looker_google_map). There is currently no way in Looker to do this without compromises.

By using `html` on the measure, we can format the value shown in the hover-over tooltips on the map, but the legend will show non-HTML values. By using `value_format` with conditionals, we can differentiate between dollar amounts and percents based on value magnitude, but there's no way to handle undecorated decimal numbers.

[nibo]

Hi, any update on this?
How can I format data labels depending on the format of the KPI the user selected via a templating filter?

[nicoleeeeeeeeee]

+1 Needing this for my clients, there are way more currencies than USD and EUR.