Diego Cordero|March 4, 2021

Video Transcript

Get the code here:

Hi, welcome back to the Corona Learning Series. My name is Diego Cordero, and I’m the BI lead and Data Strategist at Profusion. Today we’ll be looking at how to create a currency conversion switch using Sisense BloX, and some other Sisense features.


So, here’s what you’ll need, a data model with a currency table, which I’ll show you in a bit. And mine, in this case, is split by dates. You’ll need to add the currency rate to the formulas on the widgets that you want to change with the switch. You also need a filter for unique currency name column, so I’ll show you that in a bit as well. You’ll also need the BloX switch template that will be provided along with this tutorial, and the custom JavaScript code on switches script editor, these will be provided as well. Now I will show you how to edit for your use case. So, let’s jump to the demo now.


Here’s our welcome metrics dashboard with a currency converter switch. It displays sales data for avocados in the US, and it has three different types of avocados, small, large and extra large hass, and you can see here that we have split it by region, type, and date. But this is in US dollars. If I wanted to present this to our CEO, Natalie and convince her if it’s worth investing in avocados it needs to be in British Pounds, so I’ve added here a Great British Pound switch. And when we click it, all the prices, for sales are in British Pounds.


How do we achieve this? First of all, let’s look at the data model. Here we have our currency table, which is the most important one. These dates are covering the dates that are arranged here, which is early 2015, to early 2018, and we have four columns, the date, rate, current base currency, and the conversion currency. It is important to mention that the dates that you see here are duplicated. Again, after the Great British pounds entry, we have the US dollar. So, if I filter here for US dollars, we have the dates again. So basically, we have two sets of dates, with one for each currency. And you can see that the rate here is one because we’re using this as a multiplier in the formulas. The rest of the columns are a standard table that you would use in Sisense. So, this is our avocado table with all the data from the avocados, this another custom table that I created from this table just displayed by type and this is a unique date from this table, which I’m using as a filter. These are all optional and will probably depend on your use case, but it’s important to have your currency rates stable here. This was created in a Google spreadsheet, there is a formula out there that you can use to create these conversions and you can set a range date, and it will create those rates automatically for you.


So, let’s go back to our dashboard where we have our switch. By default, it is in US dollars and now we’re going to see how we’ve added these to our formulas here in our widgets. So we take, for example, our indicator, we have the average of the average price, I will never recommend to average and average. But in this case, we don’t have these already aggregated, the number probably is wrong, but just for the sake of it we’re averaging the average and we need to multiply that by the rate.

Now, in this case, I’m using a max rate and not the sum or the average, it would be the same to use the average but I’m using the max because here we have duplicated dates, as we have many dates, which basically make the right multiple by itself, or as many times as the dates appear. So, if we have one here for dollars in the database three times you will see by times three. So, we don’t want that, we just want the max and it’s worth mentioning that in my data model, all these numbers are filtered off with the date filter and the currency builder, I get the same number many times, so I can get a max, I can get an average and I can get the mean, because they’re all the same number, but not the sub, because it will just sum all of them. If I take this out, you can see that it’s the same number because it’s multiplying it by one, but I’m just going to cancel and not save that. That’s the same case for all of my charts, here, I’ve added the same rate to my formulas, and it’s the same for these two charts.


Now we’ve seen the model and how to add the currency rate to the formulas, let’s talk about the filter. So, in the filter, here, we can see, the unique names, this column here, which has great British pounds and US dollars, is the filter that will be affected by our switch. We have also added the date filter, but that’s just for my use case and this will only depend on if you have a date as well. So, now let’s see the BloX template.


In our BloX template, we have one text BloX, which is the title and is optional, and then we have a container that has a column set with three columns, one for the US dollar title, one for British Pound title, and the middle column is our switch. Now, this text BloX is actually the container of the button, the one that moves, and the text within the text BloX is the actual switch that moves. So, you can see here the background colour is grey, and the height and the width correspond to that of the background, as well as the radius border, (radius of 10) which makes it rounder. So, if you wanted a square one, you could also do that, but in my case, I want a round switch. Now the text within, it’s an HTML that creates the circle which has a box-shadow, and a gradient, it has different styles to make it look better, but a very important bit here is the transition, which is what makes it move smoothly, so that’s the style for the switch, but we have more style here at the top, and this is so that when I hover over it, it changes colour. The other bit is for when I add a class active via JavaScript, so it changes the colour to green, and it also moves the circle to the right side. So, that’s the BloX template, and now we’re going to see the JavaScript behind this.


If we click ‘edit script’, we can see that it’s this function that gets triggered whenever I click the switch. So that is the ID switch, this is something important to add into your template because it triggers the function, and what it does, if we break it down here, is it toggles the class active in the switch and the switch button so in both elements, it adds a new class here that’s active like that and it adds the class active here as well. So in the end, it looks like this whenever you click it.


So, what the script does, is toggle the class active in the switch and the switch button, and then runs an if statement that checks whether the switch button is active or not. If it is, it goes through the filters in our dashboard, so as I showed you, these ones here, it goes through those filters, it finds the one corresponding to our column called current conversion currency in this case, and this is where you would change the name of the column and it changes the filter to GBP. It selects this one programmatically and then updates the filter with this function here, and this is also where you could change your desired currency. If you have euros or you have any other currency, you can change it there. Now, what it does, is it goes through all the widgets and checks if they have a title and because I was adding a title to those widgets that I want to be changed, it’s worth mentioning that, although you don’t see the title here, it does have a title, average value, and what I did was choose to hide the title bar, so you don’t see the title, but it does have it so the code actually gets it. You could add another function here so that the title is showing, it contains code to be converted, or if it contains price, then go through those widgets as well. You can also choose not to but I would encourage you to try to filter the widgets down because it makes the script a bit more efficient.


So, after getting those widgets that do have a title, it goes through the metadata of the widget, so what it does is check the panels here, so for each panel, it checks that the name has values, so it gets this here. Or values in some cases is just called value, it’s just being more robust. So, it’s either value or values. Then it checks if it’s undefined, so it’s checking that it actually has a value, and when finding the values, it goes through the values and checks if they have a mask of currency. The mask is this one that you see here, in this case, this one has and what it does is changes that mask to pounds in this case, and at the end, it just refreshes the widget. So, what we’re doing with the JavaScript is changing the filter, changing the symbol in our chart, and refreshing the widget. If it’s not active, what it does is the same thing, but with US dollars. So, it looks very complicated, but it’s not actually that complex. But these will be provided to you along with this tutorial.


So, that is how we create a custom currency converter switch. Thank you for watching and I’m looking forward to seeing all the use cases and how you use the template and the custom script, hopefully, you enjoyed the tutorial and thank you!

Sign up to our newsletter

Want to make life easier by staying on top of market trends?