Question

Best way to handle multiple currencies

  • 20 October 2017
  • 2 replies
  • 569 views

Hi,


We currently have a table with multiple currencies, i.e. with columns like:


Revenue, Cost, Currency

5.50, 2.10, USD

3.30, 1.10, EUR


etc.


And ideally we want to prevent Looker users from summing over the Revenue without accounting for the currency (i.e. grouping by currency, country or city). What is the best way of dealing with this?


(We do not currently convert the currencies in the database)


2 replies

Userlevel 2

Hi James,


The best way to prevent users summing over multiple currencies is to force a GROUP BY clause in your measure to ensure separation in your totals.


To achieve this Looker has a required fields parameter.


The required_fields parameter allows you to pull additional fields into a query when a user chooses a specific field, in your case currency.


This forces the grouping clause.


Your measure will look like this:


 measure: totalrevenue {
type: sum
sql: ${sale_price} ;;
required_fields: [currency]
}

This will form a SQL command that looks like this:


SELECT 
order_items.currency AS `order_items.currency`,
COALESCE(SUM(order_items.sale_price ), 0) AS `order_items.totalrevenue`
FROM demo_db.order_items AS order_items

GROUP BY 1

Its is important to note however that although the required fields are added to the query, they are not displayed to the user. This can be confusing but can be overcome by adding a description in the measure that will be displayed to the user in the field picker under the i symbol.

Thanks, this seems like a good solution.

Reply