Currency Conversion Parameter and Formatting

[sam_pitcher]

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