Question

Creating a formatted money value with a dynamic currency symbol

  • 5 August 2015
  • 3 replies
  • 785 views

Userlevel 3

Sometimes your table will have two columns for a money value. One column expresses the amount as a number, and the other column provides the currency. It can be tricky to format that appropriately. You could concatenate the currency symbol with the amount, but then the value becomes a string and will sort alphabetically rather than numerically. Instead, use the html: attribute to provide a formatted value.


In your view, define a field with a name like “currency_symbol” and provide the logic to get the proper symbol. This field can be hidden since it won’t provide any useful information on its own.


Then create a formatted_amount field with an html attribute. In the html element the code {{ currency_symbol.value }} will pull the value of the currency symbol field, and the code {{ rendered_value }} will get the value of the current field with its standard formatting (comma separated thousands, etc.).


A simple view looks like this…


- view: currency_demo
derived_table:
sql: |
SELECT 98765432.23 as val, 'USD' as currency
UNION ALL
SELECT 456789.56 as val, 'EUR' as currency
UNION ALL
SELECT 86753.09 as val, 'SFr.' as currency

fields:
- dimension: currency
sql: ${TABLE}.currency

- dimension: currency_symbol
hidden: true
sql: |
CASE
WHEN ${currency} = 'USD' THEN '$'
WHEN ${currency} = 'EUR' THEN '£'
ELSE CONCAT(${currency}, ' ')
END

- dimension: formatted_amount
type: number
decimals: 2
html: |
{{ currency_symbol._value }}{{ rendered_value }}
sql: ${TABLE}.val

The result looks like this…



The sorting works on a strictly numeric basis. A better idea would be to convert the amount to some common currency in another hidden field, and then use the attribute “order_by_field” to sort the column on the common currency equivalent value of the amount.


Here is an adjusted example showing sorting implemented properly (conversions as of Aug 5, 2015).


- view: currency_demo

derived_table:
sql: |
SELECT 10000.00 as val, 'USD' as currency
UNION ALL
SELECT 10000.00 as val, 'EUR' as currency
UNION ALL
SELECT 10000.00 as val, 'CHF' as currency
UNION ALL
SELECT 10000.00 as val, 'JPY' as currency

fields:
- dimension: currency
sql: ${TABLE}.currency

- dimension: currency_symbol
hidden: true
sql: |
CASE
WHEN ${currency} = 'USD' THEN '$'
WHEN ${currency} = 'EUR' THEN '£'
WHEN ${currency} = 'JPY' THEN '¥'
ELSE CONCAT(${currency}, ' ')
END

- dimension: formatted_amount
type: number
decimals: 2
order_by_field: usd_amount
html: |
{{ currency_symbol._value }}{{ rendered_value }}
sql: ${TABLE}.val

- dimension: usd_amount
hidden: true
type: number
decimals: 2
sql: |
CASE
WHEN ${currency} = 'USD' THEN 1.0 * ${formatted_amount}
WHEN ${currency} = 'EUR' THEN 1.09 * ${formatted_amount}
WHEN ${currency} = 'JPY' THEN 0.008 * ${formatted_amount}
WHEN ${currency} = 'CHF' THEN 1.02 * ${formatted_amount}
ELSE NULL
END

The result looks like this - note that the sort is proper.


3 replies

I’ve set up measures as described above, which work great when looking at the Results tab or a Table visualisation. However, when I try to view as any other type of visualisation (e.g. Column), the currency symbols are all stripped out from both the axis and the value labels. The tooltip still shows the correct symbol though. It seems as though the visualisation strips out HTML options. Is there any way of retaining these?

For the New LookML of the second set of code:


view: currency_demo
{
derived_table:
{
sql:
SELECT 10000.00 as val, 'USD' as currency
UNION ALL
SELECT 10000.00 as val, 'EUR' as currency
UNION ALL
SELECT 10000.00 as val, 'CHF' as currency
UNION ALL
SELECT 10000.00 as val, 'JPY' as currency;;
}

dimension: currency
{
sql: ${TABLE}.currency;;
}

dimension: currency_symbol
{
hidden: yes
sql:
CASE
WHEN ${currency} = 'USD' THEN '$'
WHEN ${currency} = 'EUR' THEN '£'
WHEN ${currency} = 'JPY' THEN '¥'
ELSE CONCAT(${currency}, ' ')
END;;
}

dimension: formatted_amount
{
type: number
value_format: "0.##"
order_by_field: usd_amount
html:
{{ currency_symbol._value }}{{ rendered_value }};;
sql: ${TABLE}.val;;
}

dimension: usd_amount
{
hidden: yes
type: number
value_format: "0.##"
sql:
CASE
WHEN ${currency} = 'USD' THEN 1.0 * ${formatted_amount}
WHEN ${currency} = 'EUR' THEN 1.09 * ${formatted_amount}
WHEN ${currency} = 'JPY' THEN 0.008 * ${formatted_amount}
WHEN ${currency} = 'CHF' THEN 1.02 * ${formatted_amount}
ELSE NULL
END;;
}

}
Userlevel 3

Of course you probably won’t hardcode the currency conversion, but instead do a lookup in a table in your database. Your business needs will determine if you want to do the conversion based on the rate on the date of the transaction, or if you want to use the most recent rate.

Reply