Question

Currency Conversion Parameter and Formatting

  • 24 June 2020
  • 4 replies
  • 2628 views

Userlevel 2

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


4 replies

Userlevel 5
Badge

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.

Userlevel 4

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

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

Reply