Currency Conversion Parameter and Formatting

It’s common to want to switch between currencies in Looker. This is usually achieved by having your results/table in a base currency, for this example EURO.

We can then have a currency conversion table that is joined in and you can convert the base currency to a local currency. Let’s define the currency conversion table in a view called currency_conversion. This will have three columns:

  • Conversion Rate: the conversion from Euro’s to Local Currency
  • Country: this is the country that is joined to the price table
  • Currency Symbol: this is the symbol for the currency, ie £ for GBP
view: currency_conversion {
  sql_table_name: currency_conversion ;;
  
  dimension: conversion_rate {
    hidden: yes
    type: number
    sql: ${TABLE}.conversion_rate ;;
  }
  
  dimension: country {
    hidden: yes
    type: string
    sql: ${TABLE}.country ;;
  }
  
  dimension: currency_symbol {
    hidden: yes
    sql: ${TABLE}.currency_symbol ;;
  }
  
}

In the view that has our price dimension, we can add a parameter:

parameter: currency_parameter {
    label: "Currency"
    type: unquoted
    default_value: "EURO"
    allowed_value: {
      label: "Euro"
      value: "EURO"
    }
    allowed_value: {
      label: "Local Currency"
      value: "LOCAL"
    }
  }

Then for each dimension that needs to be converted, we can set up hidden _euro and _local dimensions:

  dimension: price_raw {
    hidden: yes
    type: number
    sql: ${TABLE}.price ;;
  }

  dimension: price_euro {
    hidden: yes
    type: number
    sql: ${price_euro_raw} ;;
    value_format_name: decimal_2
  }

  dimension: price_local {
    hidden: yes
    type: number
    sql: 1.0 * ${price_euro} * ${currency_conversion.conversion_rate} ;;
    value_format_name: decimal_2
  }

We can then reference these using our parameter in another dimension called price_currency:

dimension: price_currency {
  label: "Price"
  type: number
  sql:
    {% if currency_parameter._parameter_value == 'EURO' %}
      ${price_euro}
    {% else %}
      ${price_local}
    {% endif %} ;;
  value_format_name: decimal_2
  }

This liquid is saying if the user has selected Euro, then use the price_euro dimension. Otherwise we want it in local currency.

This will work nicely, however the format of the dimension will be to 2 decimal places with no currency symbol. We can use the html parameter to get round this! Now this html can be reused, so it would make sense to store it as a constant in a manifest file.

constant: currency_html {
  value: "
  {% if currency_parameter._parameter_value == 'EURO' %}
  €
  {% else %}
  {{ currency_conversion.currency_symbol._value }}
  {% else %}
  €
  {% endif %}
"
}

This liquid is doing the same as above, however it now returns € if the user selects Euro, or the correct currency symbol if local currency is selected.

Tying this together, we can add html to our price_currency dimension.

html: @{currency_html}{{rendered_value}} ;;

This will use the liquid for the correct currency symbol and add it before the rendered_value of the dimension, ie 2 decimal places.

This all works really nicely in principal, however there is something else that can catch users out. Using this, you could potentially have a single value viz that will try to sum local currencies together. This is not good, as you should not sum Euros with Pounds with Dollars… it doesn’t work. So we can add another layer of liquid to handle this in our price_currency dimension:

  dimension: price_currency {
    label: "Price"
    type: number
    sql:
    {% if currency_parameter._parameter_value == 'EURO' %}
      ${price_euro}
    {% else %}
      {% if country._in_query %}
        ${price_local}
      {% else %}
        ${price_euro}
      {% endif %}
    {% endif %} ;;
    value_format_name: decimal_2
    html: @{currency_html}{{rendered_value}} ;;
  }

and the same for the constant in the manifest:

constant: currency_html {
  value: "
  {% if currency_parameter._parameter_value == 'EURO' %}
  €
  {% else %}
  {% if country._in_query %}
  {{ currency_conversion.currency_symbol._value }}
  {% else %}
  €
  {% endif %}
  {% endif %}"
}

Now, if your country dimension is not in the query, the result will be in Euros, even if the user has selected Local Currency. If the country dimension is in the query, then this means that the result will be split by country, so the result of the query will be good to show in Local Currency!

The final piece is joining the currency_conversion view to the price view on the country dimensions and then you can create measures off the price_currency dimension, set the value_format_name to decimal_2 and use the same html_currency constant.

  measure: total_price_currency {
    label: "Total Price"
    type: sum
    sql: ${price_currency} ;;
    value_format_name: decimal_2
    html: @{currency_html}{{rendered_value}} ;;
  }

But wait! There’s more, if the value of the currency is negative, then this would return €-0.99 for example, when really you’d want the rendered value to show -€0.99. The answer, more liquid.

We can amend our currency_html constant to include the value of the dimension as well:

constant: currency_html {
  value: "
    {% if currency_parameter._parameter_value == 'EURO' %}
      {% assign currency_symbol = '€' %}
    {% else %}
      {% if country._in_query %}
        {% assign currency_symbol = currency_conversion.currency_symbol._value %}
      {% else %}
        {% assign currency_symbol = '€' %}
      {% endif %}
    {% endif %}

  {% if value > 0 %}
    {% assign currency_value = currency_symbol | append: rendered_value %}
  {% else %}
    {% assign abs_value = value | replace: '-', '' %}
    {% assign currency_value = '-' | append: currency_symbol | append: abs_value %}
  {% endif %}

  {{ currency_value }}
"
}

And therefore the measure html would now only need to be @currency_html

  measure: total_price_currency {
    label: "Total Price"
    type: sum
    sql: ${price_currency} ;;
    value_format_name: decimal_2
    html: @{currency_html} ;;
  }

QED

6 7 9,120
7 REPLIES 7

Very Cool Sam!

We built something similar to support Salesforce multi-currencies models.
Essentially instead of a parameter, it’s the underlying value of the dimension currency_iso_code for the lead/opp that is used to display the correct format and conversion.

moebe
Participant V

@sam_pitcher
Great stuff
But am I right that this solutions means, that customer can not have own formatting anymore, because of the use of the html parameter with liquid?

Thanks @sam_pitcher!

Anyone was able to make this work on a visual different from table? Say like Column, Line, or Single value tile, because for these use cases the html is being striped off. So for those cases, I'm not being able to make it work.

hello @achamyan , did you by any chance find a solution to this issue? Jumping into the same one as well and table visuals is not serving our needs here...

Sorry no, I actually didn't work on this since then so haven't checked if there has been any progress on the newer Looeker versions. 

@sam_pitcher,

I’ve multi-currency model. I’m using this html

html: @{currency_html}{{rendered_value}} ;;

this is working fine in case of  positive values… but not for negative one.

For Example:

  • Results of above html is $-123,123.12
    • it should be like this -$123,123.12

How can i fix this problem?

Roderick
Community Manager
Community Manager

@adeel_nazir - not sure if this issue is still troubling you, but to fix the problem of negative values not displaying correctly in Looker, you can use the following steps:

  1. In your LookML file, add the following constant:

constant: currency_html { value: " {% if rendered_value < 0 %} -{{ currency_symbol }} {% else %} {{ currency_symbol }} {% endif %} {{ rendered_value }} " }

  1. In your dimension or measure, set the html property to @{currency_html}{{rendered_value}}.

For example:

Code snippet
dimension: price_currency {
  label: "Price"
  type: number
  sql: ${price_euro}
  value_format_name: decimal_2
  html: @{currency_html}{{rendered_value}}
}
  1. Save your LookML file and reload your dashboard.

After following these steps, negative values should now display correctly in your Looker dashboard.

Here is an explanation of the code:

  • The constant: currency_html constant defines the HTML that will be used to format the currency symbol and value. The {% if rendered_value < 0 %} conditional statement checks if the rendered value is negative. If it is, the -{{ currency_symbol }} string is prepended to the value. Otherwise, the value is simply displayed.
  • The html property on the dimension or measure sets the HTML that will be used to format the value. In this case, we are using the @{currency_html}{{rendered_value}} syntax to reference the currency_html constant.

I hope this helps! Let me know if you have any other questions.

 

Top Labels in this Space
Top Solution Authors