cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member

mceclip1.png Analytical Need 

The data is stored in a certain currency and you would like to present it in the dashboard converted to different currencies.

 Modeling Challenge

As a designer, I would like to have a dashboard filter of currencies in order to switch between the filters.

Solution

How to manage multiple currencies in one dashboard?
Many times you would like to present the values in the dashboard in different currencies. 
How to manage this depends on how your data is manged:
  1. You have only one currency managed in the data (for example everything is in USD)
  2. You have all the different currencies in the Data
Here are the different design options:

1. You have only one currency managed in the data (for example: everything is in USD)

In the example below, we want to calculate the service revenue per day.
Your basic data contains the revenue in USD of each service per day.
The best way to manage the conversion rate is by adding a currency conversion table in the ElastiCube
There are many services that provide a service of conversion rate data. 
In the example below I used a free conversion rate data in XML that provides daily data but you can use any other XML conversion rate data. In order to save the data you will need to connect this with Sisense XML connector and build this accumulative built in the Elasticube.
You can also use REST API currency conversion.
The Elasticube model contains your Revenue in USD data (The table above) and connected to the Currencies table using the Conversion Date as key.
  mceclip3.png
In the dashboard make sure the currency filter is set to singe selection (There are multiple currencies in the table, you can limit it to less currencies by using a background filter or in a custom table)
mceclip4.png
In the values presented, you will need to create a formula for (Avg Exchange Rate * Total Revenue) and then when you switch the dashboard filter the value will automatically be affected.
mceclip5.png

2. You have all the different currencies in the Data

You will need to change the modeling of the table in order to be able to filter in the dashboard according to the currency.
Your data looks probably like this:
In order to filter on the currency type we need to hold all currencies in the same column and also to add an additional column with currency description. This can be done in a custom SQL table in the Elasticube using the Union function to union the different currencies under the same column.
This should be the table's syntax: 
Select Date, Service_ID, Revenue_in_USD as Revenue , 'USD' as Revenue_Currency

union all

Select Date, Service_ID, Revenue_in_Euro , 'EUR'

union all

Select Date, Service_ID, Revenue_in_INR , 'INR'

Your data now should looks like this:
Now you can add a filter in the dashboard and switch between the different currencies.


Rate this article:
Version history
Last update:
‎02-07-2024 01:35 PM
Updated by: