User Parameters Use Case - Automatic Currency Conversion

[TriAnthony]

User Parameters Use Case - Automatic Currency Conversion

This article explains one of the example use cases of User Parameters laid out in User Parameters - What They Are and What They're For (Example Use Cases).

In addition to connection settings, User Parameters can also be used in table queries in live models to provide tailored data to end users. One of the most common uses of this type of parameter is for customizing a formula to achieve an automatic localization, e.g. currency conversion.

The idea is to use a parameter to associate each user group to their respective currency, then use the parameter to customize the table query and convert the default currency to users' local currencies. Depending on your requirements, you can use parameters to store a static exchange rate, or the currency code to allow for dynamic currency conversion.

Let's walk through an example of each type of currency conversion.

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:

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
     , P.STATUS
     , PO.CUSTOMERID
FROM "INSURANCE_CLAIMS"."PAYMENTS" P
JOIN "INSURANCE_CLAIMS"."POLICIES" PO
ON P."POLICY_KEY" = PO."POLICY_KEY"

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: