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