Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Sep 21, 2022 5:11:51 PM
Dec 22, 2017 11:07:39 AM
Being able to display data in different currencies has become a must have feature for business intelligence and analytics apps.
So far I was not able to find a solution that fits all purposes, but I have come up with 2 approaches that can be used alternatively depending on context and user needs. Attached you can find a very basic example for both approaches.
So far I was not able to find a solution that fits all purposes, but I have come up with 2 valid approaches that can be used alternatively, depending on context and user needs. Attached you can find a very basic example for both approaches.
In both cases you will find 2 concepts: Local Currency and International Currency. The first is what the source systems provide and it tells you what currency was used for the transaction that has been recorded. The International Currency, on the other hand, is a reference currency created by QV (or by an OLAP system in general) to reconcile different transactions.
You can choose to have more than one International Currency in your OLAP system and the resources to calculate and store each of them will scale linearly as the effort needed for implementation and maintenance.
Approach 1 - Runtime conversion: This approach consists in converting everything to the international currency in the ETL and convert again everything to the currency selected by the user at runtime. This approach is very flexible because it will allow to provide analysis in as many currencies as the user needs and it can be easily adapted to work with historical exchange rates. At the same time it could have some scalability and performance issues for very large applications. To go more in detail we will consider an example in which we have a fact table (Sales) which contains the price and the quantity of each product sold. All prices have already been converted to Euro (our International Currency for this example) during the ETL phases.
The fact table is then linked to a dimension table which contains all the exchange rates for the needed currencies. The 2 tables are linked using the International Currency Code and prices can be multiplied by the right exchange rate when needed. For this application to work it is needed to have one currency always selected.
2 variables are calculated in order to display money measures in the right format:
LET vCurrencySymbol = '=Only(CurrencySymbol)'; This variable can be used in charts to show the right currency symbol next to the axis.
SET vMoneyFormatting = "=vCurrencySymbol&' #.##0'"; This variable can be used in all the num() functions to convert the expression to the right format
When calculating an expression that should be converted to the selected currency the amount to be displayed must be multiplied by the right exchange rate.
Below an example of an expression to calculate the total revenue:
num(sum((Price*Sales)*ExchangeRate), vMoneyFormatting)
Below an example of an app which is using this approach:
Selected currency: EUR
Selected currency: GBP
The always one selected value must be checked in the currency ListBox for this approach to work.
Approach 2 - Batch conversion: This approach is less flexible than the one described previously, but it scales better with big applications. In this case currencies won’t be stored in rows but in columns and all currency conversions will be calculated during the batch/reload phase. This means that the application will be faster, but it will be much more difficult to allow users to view data in more than one currency. In the attached example in fact we have assumed that for the end user would be enough to see numbers in Local Currency or in International Currency (in this case Euros). Aggregates of countries which do not have the same currency would be possible only when using the International Currency (you can’t sum Euros and Dollars). At the same time we want our user to be able to switch between Local and International currencies when the Local currency can be used (the user is looking at an aggregation of countries with the same currency).
To use this approach the fact table is enough: there is no need of an additional currency dimension.
4 columns are needed to use this approach:
5 variables are created in the script of the application:
LET vSelectedCurrency = 'International';
LET vUsedCurrency = replace('=if(vSelectedCurrency=|Local| and count(distinct CurrencySymbolLocal)=1,|Local|, |International|)', '|',chr(39));
SET vPrice = "=if(vUsedCurrency='Local', 'PriceLocal', 'PriceInternational')";
LET vCurrencySymbol = replace('=if(vUsedCurrency=|Local|, Only(CurrencySymbolLocal), Only(CurrencySymbolInternational))', '|',chr(39));
SET vMoneyFormatting = "=vCurrencySymbol&' #.##0'";
Below an example of an expression that would calculate the total revenue: num(sum(($(vPrice)*Sales)), vMoneyFormatting)
Finally an example of data displayed in international currency:
and one with local currency selected:
I hope this could be useful.