Implementing a solution that enables users to display dashboard widgets in the currency of their choice using historical conversion rates
Suppose the users of an Einstein Analytics dashboard are active in different countries with different currencies, while the transactional data that in the source data of the dashboard is stored in a central currency: how could we satisfy the requirement to display all monetary data in the preferred currency of each user in a structured and reliable way?
In this blog a solution to this question is described in a step by step approach. In order to implement a similar solution yourself, what you need is:
Source data from a source system (Salesforce CRM or some other application) in which all monetary values are in a central currency (€ in this blog)
A database table (whether in an application or a static CSV file) containing the historical conversion rates from the central currency to all other relevant currencies
Knowledge of these Einstein Analytics components: dataflow transformations, custom queries & bindings
The solution will lead to a dashboard with a currency filter widget through which a user can transform all charts and tables into the currency of his choice:
Implementing the solution
The Conversion Rates dataset
To be able to convert any monetary value into another currency we need to know the historical conversion rate of that currency at the correct moment in time. Therefore, a historical conversion rates dataset is needed in Einstein Analytics. The source of this dataset can be any of the following:
Conversion Rates Custom Object in Salesforce
Database table from an external application
The structure of this dataset is simple, the following columns are needed:
IsoCode: the code of the specific currency, in our case it contains these values:
GBP (Great British Pounds)
SEK (Swedish Krona)
CHF (Swiss Francs)
RUB (Russian Ruble)
IsoPeriodKey: a combination of IsoCode, Year and Month, like ‘GBP201601’
Using a custom object in Salesforce is one option to administer the historical conversion rates, in this case the IsoPeriodKey can be built through a standard formula field and the records can look like this:
In case of using an external application as source, the IsoPeriodKey field can be created using a ComputeExpression transformation in dataflow in Einstein Analytics. When using CSV uploads, you could either calculate the IsoPeriodKey values in dataflow or build them in the CSV file itself.
The Conversion Rates dataset provides us, for every month in the relevant history, the appropriate conversion rate from the central currency to the target currency.
In this blog’s scenario we will work with the Opportunity object from Salesforce, from which we collect data of 4 fields: Name, StageName, CloseDate and Amount. In the dataflow we want to establish that the Amount field (which is in EUR) is converted into the other currencies (GBP, SEK, CHF and RUB) using the historical conversion rate that was applicable on the CloseDate of the Opportunity. We will do this using Augment transformations to the Conversion Rates dataset. The complete dataflow looks like this:
The first part of the dataflow (the top row) is about extending the Opportunities table:
Digest the Opportunity object from Salesforce
Add one field per target currency using a Compute Expression transformation.
3. Calculate the Month and Year using a Compute Expression transformation (for the Month field set Precision to 2, for the Year field set Precision to 4)
4. Create the key fields, which are a combination of IsoCode, Month and Year
The next part of the dataflow (bottom part of the dataflow image) is about incorporating the correct conversion rate for each currency:
Digest the Conversion Rates dataset
For each target currency we need an Augment transformation that finds the correct conversion rate (which happens by matching the key field on Opportunity stream with the IsoPeriodKey field in the Conversion Rates dataset
Left Source: [“ce_CreateKeyFields”]
Left Key: [“GBPKey”]
Right Source: [“sfdcDigest_Conversion_Rate__c”]
Right Key: [“IsoCodePeriodKey__c”]
Right Fields: [“Conversion_Rate__c”]
Operation: Look Up Single Value
A similar Augment transformation is implemented for each other target currency
Using these Augments we bring the correct conversion rates to the Opportunities dataset
3. Convert the amount field into each currency (set Precision to 16 and Decimals to 2)
4. Register the enriched Opportunities dataset
Once this dataset has run, the resulting Opportunities dataset contains an Amount column for each currency:
Bindings and The Dashboard
In the dashboard there is a bar chart that displays Opportunity Amount by StageName, with the Amount in EUR. Through a list widget we want the chart to update, displaying the Amount in the chosen company. In Einstein Analytics terms this kind of mechanic is referred to as ‘dynamic measure’ (meaning that the Y-axis of a bar chart changes based on a selection).
In order to achieve this behaviour a couple of steps are needed:
We create a custom query (this is the new name for a ‘static step’) containing the currency IsoCodes and implement it on a list widget that is set to ‘Single-selection (required)’. In the dashboard JSON we set the parameters for this custom query as in the image below. The “Display” value is what appears in the drop-down list, the “mea” value is what we will pass to the chart widget in order to change the measure upon selecting a currency.
2. In the dashboard JSON we implement the binding that will make the chart update based on the selected currency like this:
The measure is no longer static, but it depends on the selection of the Currency_1 custom query. It is a dynamic measure now, exactly as we needed it to be to establish our goal!
While historical conversion rates can be implemented in Salesforce setup, this feature is not a great match for our use case as in this feature a conversion rate (for a specific currency) has a start date and an end date. In the Augment transformation in dataflow you want to match a row (that has a date field) with a row of the Conversion Rates dataset, which is hard to establish when the Conversion Rates rows do not have a consistent key but rather a time interval.
Historical Conversion Rates on a Daily Basis
In the described solution in this blog, historical currency rates are maintained on a monthly basis. In case conversion rates can be changed on a daily basis, a similar solution can be implemented:
The Conversion Rates dataset should be extended with a ‘Day’ field and this ‘Day’ field should also be incorporated into the IsoPeriodKey field.
The date fields in the source dataset (Opportunities in our example) should be broken down into Year, Month and Date so that the proper key can be created using a computeExpression transformation.
With that, each date field in each row of the source dataset can be matched with a row in the Conversion Rates dataset and the conversion can be done based on that.
Multiple monetary fields in a dataset
In case there are multiple monetary fields in a dataset (and they need to be converted), this comes down to creating key fields per monetary field and then doing the append transformations for each target currency for each monetary field. With many monetary fields in a dataset the described solution probably is not great. If you have a better approach for that, I’d love to hear about it!
https://www.nextview.nl/wp-content/uploads/2020/01/Einstein.png256256nextviewhttps://www.nextview.nl/wp-content/uploads/2018/01/nextview-logo.svgnextview2020-01-24 13:25:082020-01-24 13:25:08Multi-Currency in Einstein Analytics
Nextview Design Thinking Center
Willem Fenengastraat 4C
1096 BN Amsterdam
+31 (0)85 0043065
Nextview Design Thinking Center
High Tech Campus 27
5656 AE Eindhoven
Click on the different category headings to find out more. You can also change some of your preferences. Note that blocking some types of cookies may impact your experience on our websites and the services we are able to offer.
Essential Website Cookies
These cookies are strictly necessary to provide you with services available through our website and to use some of its features.
We provide you with a list of stored cookies on your computer in our domain so you can check what we stored. Due to security reasons we are not able to show or modify cookies from other domains. You can check these in your browser security settings.
Google Analytics Cookies
These cookies collect information that is used either in aggregate form to help us understand how our website is being used or how effective our marketing campaigns are, or to help us customize our website and application for you in order to enhance your experience.
If you do not want that we track your visit to our site you can disable tracking in your browser here:
Other external services
We also use different external services like Google Webfonts, Google Maps, and external Video providers. Since these providers may collect personal data like your IP address we allow you to block them here. Please be aware that this might heavily reduce the functionality and appearance of our site. Changes will take effect once you reload the page.
Google Webfont Settings:
Google Map Settings:
Google reCaptcha Settings:
Vimeo and Youtube video embeds:
The following cookies are also needed - You can choose if you want to allow them: