Question

Dynamic value format name

  • 21 September 2020
  • 6 replies
  • 1230 views

Userlevel 7
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 4

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.

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

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