Question

Dynamic value format name

  • 21 September 2020
  • 6 replies
  • 960 views

Userlevel 6
Badge +1

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


6 replies

Userlevel 6
Badge +1

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!

Userlevel 3

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.

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.  

Userlevel 6
Badge +1

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

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)!

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>  ;;

    }

Reply