Looker Community

Creating a formatted money value with a dynamic currency symbol

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 Likes

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.

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;;
  }
  
}
2 Likes

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?