Question

Dynamic value format name

  • 21 September 2020
  • 3 replies
  • 820 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


3 replies

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.

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!

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.  

Reply