#ThrowbackThursday to our first task during dashboard week.

We needed to use the FOAAS API to download data and visualise it in Tableau. The API returned a series of phrases and for my visualisation, I used parameters to allow the user to personalise parts of each individual phrase but changing words to their own.

I encountered some problems because to begin with, I used one long IF statement. The parameters would work for some but not all of the words, and it worked in some sentences and not in others. I didn’t understand why the calculations seemed so fickle, until I read a bit more about how Tableau handles IF statements.

Tableau will start with on a row and move down the IF statement, checking that row against all the clauses. When the IF clause is “True” the value in the row is changed, and Tableau moves onto the next row of the data. It doesn’t search each row for all of the IF statements; once it’s found the one which is true, it stops. This explains why in my dashboard some values would change in some sentences but not in others, it all depended on the order of the IF statement.

For example, with the following formula, if there was a sentence with which had “black cat” the “cat” would change because that would be the first IF in the field which is true. However, “black” would not change, because once Tableau sees that the statement is true at “cat” it then moves on to the next row of data.

The way I got round this was to write a series of IF statements, one for every single parameter, and then refer them back to the previous in sequence. Make sense? Let’s go through the steps with an example.

So let’s look at an example where we want to change three words in the sentence: “The fat black cat sat on the orange rug”, where each of the sentences are a separate row in the data.

You can see that I’ve already built my parameters, where you can type in your preferred animals and the colours you’d like the animal and rug to be.

I’ll need three separate IF statements to do this.

The first IF statement will refer to the parameter for the animal and change “cat”:

The second IF statement will refer to the results from the calculation to change the animal, the parameter to change the animal’s colour, and will change “black”:

The third IF statement will refer to the calculated field which changes the animal’s colour and the parameter specifying the rug to change “orange”:

And so on.

The issue with doing this in sequence means that it takes Tableau a while to go through each of the statements, and the visualisation could renders a little slowly with a large dataset or a lot of parameters. Luckily our collection of sentences from FOASS in Dashboard week was mercifully small (especially if you know the subject matter), because otherwise it would’ve have been a while.