Dynamic value format name

  • 21 September 2020
  • 3 replies

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" %}
{% elsif conversion_currency._parameter_value == "USD" %}
{% else %}
{% 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" %}
{% elsif conversion_currency._parameter_value == "USD" %}
{% else %}
{% 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:

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


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.