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.
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.
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, 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!