a. Static Conversion
For simple static conversion, the values of the parameter should represent the exchange rates of the default currency to users' local currencies. When creating the parameter, be sure to select Custom Live Queries in the Applicable Areas field, and Number in the Field Type field. Here is how the exchange rate parameter should be defined:
Once you've created the user parameter, the next step is to assign a value for each group (or individual user, if needed). Here is an example of user groups and value assignment for the exchange rate parameter, assuming the default currency is US Dollar (USD):
Now that the parameter values have been assigned to groups, the final step is to create/modify your table query(ies). Let's take a table called FactPayments in an insurance company's data model as an example. This is the original query of the table:
In this example, the amount of funds paid to customers for their insurance claim is stored in USD in a column called PAIDAMOUNT. To convert the payment amount to users' local currencies, multiply the PAIDAMOUNT column by the value of the user parameter. To add a parameter to a query, type two curly brackets, then select the parameter from the dropdown menu. Here is what the conversion formula should look like:
PAIDAMOUNT * {{EXCHANGE_RATE}}Here is the full, updated query of the FactPayments table:
SELECT
P.CLAIM_KEY
, P.POLICY_KEY
, P.PAYMENT_DT_TIME
, CONVERT_TIMEZONE('UTC', '{{TIMEZONE}}', PAYMENT_DT_TIME) AS PAYMENT_DT_TIME_LOCAL
, DATE_TRUNC('DAY', PAYMENT_DT_TIME_LOCAL) AS PAYMENT_DT_LOCAL
, P.PAIDAMOUNT
, {{EXCHANGE_RATE}} AS EXCHANGE_RATE
, P.PAIDAMOUNT * {{EXCHANGE_RATE}} AS PAIDAMOUNT_LOCAL
, P.STATUS
, PO.CUSTOMERID
FROM "INSURANCE_CLAIMS"."PAYMENTS" P
JOIN "INSURANCE_CLAIMS"."POLICIES" PO
ON P.POLICY_KEY = PO.POLICY_KEY
Save the query and repeat the same step for all other tables that require currency conversion. Once completed, republish the model and revisit your dashboard. For this example, here is how the data originally looks with payment amount in USD:
Here is how it looks for users in the Japan user group, for which the payment amount is converted with a static exchange rate of 151.7:
b. Dynamic Conversion
For a more complex conversion where historical exchange rate fluctuations need to be taken into account, a history table of currency exchange rates is required. This data is typically readily available within your organization or can be collected from public sources, such as the IMF. Here's an example of a historical exchange rate table (one date per currency):
The values of the parameter should reflect the IDs of users' local currencies as referenced in the exchange rates history table. In this example, they should correspond to the values in the CURRENCY_CODE field in the history table above. Note that whitespace is not currently supported in parameter values. Therefore, using the full currency names as values, such as "Japanese Yen," will not be accepted. Here is how the currency code parameter should be defined:
Here is an example of user groups and value assignment for the currency code parameter:
In the context of the FactPayments table example, to convert the payment amount to users' local currencies using the exchange rate effective on the payment date, add a join in the query with the historical exchange rate table. The join conditions should be set on the currency code and date fields, where the currency code from the history table matches the value of the user parameter. Here is how the join should look like:
JOIN "INSURANCE_CLAIMS"."EXCHANGE_RATE" X
ON X.CURRENCY_CODE = '{{CURRENCY_CODE}}'
AND PAYMENT_DT_LOCAL = X.DATE
Here is the full, updated query of the FactPayments table:
SELECT
P.CLAIM_KEY
, P.POLICY_KEY
, P.PAYMENT_DT_TIME
, CONVERT_TIMEZONE('UTC', '{{TIMEZONE}}', PAYMENT_DT_TIME) AS PAYMENT_DT_TIME_LOCAL
, DATE_TRUNC('DAY', PAYMENT_DT_TIME_LOCAL) AS PAYMENT_DT_LOCAL
, P.PAIDAMOUNT
, X.EXCHANGE_RATE AS EXCHANGE_RATE
, P.PAIDAMOUNT * X.EXCHANGE_RATE AS PAIDAMOUNT_LOCAL
, '{{CURRENCY_CODE}}' AS CURRENCY_CODE
, P.STATUS
, PO.CUSTOMERID
FROM "INSURANCE_CLAIMS"."PAYMENTS" P
JOIN "INSURANCE_CLAIMS"."POLICIES" PO
ON P.POLICY_KEY = PO.POLICY_KEY
JOIN "INSURANCE_CLAIMS"."EXCHANGE_RATE" X
ON X.CURRENCY_CODE = '{{CURRENCY_CODE}}'
AND PAYMENT_DT_LOCAL = X.DATE
Save the query and repeat the same step for all other tables that require currency conversion.
Here is how the data originally looks with payment amount in USD:
Here is how it looks for users in the Japan user group, for which the payment amount is converted using the USD-JPY exchange rate applicable on the payment date:
