MENU
GET LISTED
GET LISTED
SHOW ALLPOPULAR CATEGORIES

How To Convert Currencies With Sisense: Analyzing International Data

Sisense: No. 1 BI Software

USER SATISFACTION 99%
OUR SCORE 9.7

ssSisense has been awarded multiple times for its capacity to allow companies to picture important data, and transform it into a workable plan. Alongside interactive dashboards and valuable insights, Sisense is preferred by many companies for its capacity to analyze international financial data in an unparalleled way. This is mostly due to the fact it uses integrated data connectors and connects to a wide range of external web applications. Qlik Sense, for instance, which another reputed BI provider, doesn’t offer such functionality (you can read more about it in our Sisense vs Qlik Sense comparison).

Displaying costs and revenues across currencies is actually a pretty simple operation, but there aren’t many Sisense reviews that can help novice users get hold of it. Once you start using it, Sisense stores data in USD as its basic currency, but tracks and refreshes exchange rates day by day, in case you decide to format numbers in a different data model. For those who still don’t know it, Sisense offers a specially developed conversion plugin which contains and updates exchange rates on daily basis. But how does this process work? We explain in our Sisense tutorials:

Step #1: Enable your new data model

The first thing to do is to locate and open your sales database, where unless you’ve performed some extensive customization, transactions will appear as Order Headers and Order Detail. Most cross-currency related operations are gathered in the Sisense ElastiCube Manager, where you will find attached the Currency Conversion Elasticube. Open it, and give its content a look.

The first thing you will see there is a list of referenced customers, and the Order Header which collates details about each order, including locations, dates, and payment data. The Line Total which is calculated in this section is actually the value you are trying to convert.

Moving down, you will see an Exchange Rates table where all currency options are listed with their current value. Have in mind that all rate types will only apply if you’ve required them, namely if you’ve filled the Exchange Rate Currency and Exchange Rate Type filters available on your dashboard. This is done to facilitate operations for users that don’t need to analyze international data.

The next step is to connect the tables to each other, paying special attention to the relationship between your Order Header and the Exchange Rate Table (once filters are enabled, your Line Total will be automatically calculated in the desired currencies).

Step #2: Configure your Currency Conversion Plugin

You are probably familiar with the fact that currency conversion in Sisense requires a special plugin which is not pre-installed in your system, but must be downloaded and unzipped to use, and then transferred to your local C:\Program Files\Sisense\PrismWeb\plugins folder. Inside, there will be a settings.js file, the actual location where you need to configure currency conversion. The only variable inside that needs modification is the currencyElasticube one, using the following code:

var currencyElasticubes = {

    ‘CurrencyConversion’: {

           ‘filters’: [

                   {

                           ‘dim’:'[Exchange Rates.Exchange Rate Currency]’,

                           ‘label’: ‘Currency’,

                           ‘datatype’: ‘text’,

                           ‘default’: ‘USD’,

                           ‘isCurrencyFilter’:true

                   },

                   {

                           ‘dim’:'[Exchange Rates.Exchange Rate Type]’,

                           ‘label’: ‘Exchange Rate Type’,

                           ‘datatype’: ‘text’,

                           ‘default’: ‘A’,

                           ‘isCurrencyFilter’:false

                   }

           ],

           ‘formulas’:[‘Sales Amount’,’Avg Unit Price’],

           ‘symbols’: {

                   ‘default’:”,

                   ‘USD’:’$’,

                   ‘EUR’:’€’,

                   ‘CAD’:’C$’,

                   ‘GBP’:’£’,

                   ‘JPY’:’¥’

           }

    }   

}

var enabledByDefault = true;

 

What are the terms you will encounter here, and what do they mean?

The currencyElasticubes are system objects that need to be converted. With Elasticube as the keyword, all values will be displayed in a format similar to the previously displayed code.

Filters are check-fields where you can specify details about your Elasticube, while dims are the columns and tables that display all Elasticube information. Each Filter has its own Label, and is displayed on your dashboard with default value. The most important Filter in this case is the CurrencyFilter and its flag-list, where you select the symbol that is going to appear in each of the widgets.

Next to the filters, you will find a table full of formulas you can use to convert values into another currency. All formulas use the exchange rates listed in the system, which makes it possible to count the exact amount you’ve sold.

Finally, the system provides a dictionary of currency symbols used to identify different options, but in case you find them difficult to differ, you can customize your own codes inside the Exchange Rate table.

Step #3: Design your Dashboard

The first two steps intend to complete your data model and to prepare the plugin configuration, but real conversions based on exchange rates happen in the last phase of the process. Now, you need to use all data calculated in the data model to design a conversion dashboard.

But why do you need the dashboard? The answer is pretty simple: it is on the Dashboard where you are going to see all required filters you specified in the settings.js file, and check revenue/costs for a single currency at a time instead of getting confused with all of them. If interested, you can also add other widgets and perform other calculations, but remember to add the exchange rate formula for all of them.  

The basic formula you need to create is:

SUM([LineTotal All Items] /[Exchange Rate All Items])

 

In this case, the specified field must always be All Items, so that each value will be divided with its corresponding exchange rates. You don’t have to insert the formula all over again: make sure you’ve clicked on the star icon next to it, and that the name matches the one inserted in your settings.js file.

Once again, don’t forget to check the necessary filters on your Dashboard, because failing to do so could result in duplicated amounts for each currency listed in the Exchange Rate table. Note that the system doesn’t offer you an option to remove or disable these filters automatically.

We hope our Sisense tutorials have made everything clear and help you make the optimum use of the software for your special needs.

Category: B2B News

Leave a comment!

Add your comment below.

Be nice. Keep it clean. Stay on topic. No spam.

You can use these tags:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This is a Gravatar-enabled weblog. To get your own globally-recognized-avatar, please register at Gravatar.

I agree to publishing my personal information provided in this comment.

Page last modified

Share
Tweet
+1
Share