As a data analyst, one of the most satisfying things is creating successful calculations. However, getting it right the first time can be challenging, and I’ve spent countless hours trying to properly frame code only to find out a comma was in the wrong place or a parenthesis was missing. It takes a deep understanding of data analytics tools and experience to ensure that the calculations are accurate.
When creating dashboards, many calculations require the use of IF/THEN statements. While this has been my go-to calculation for many years, I suggest avoiding it unless you know your data analytics tools like the back of your hand. The IF/THEN statement is often used to set up a certain number of rules that the user requires for their dashboard. For example, IF Column A = “Rice” THEN (new column) = “1” ELSEIF Column A = “Wheat” THEN (new column) = “2”. This calculation creates a new column that has a “1” if Column A is “Rice” and “2” if it’s “Wheat”. However, when there are 50+ variations of this, they can become far more complicated, such as referencing other columns or referencing them based on certain parameters.
As analysts, our primary focus is automating steps and doing all the manual steps ourselves so that the user never has to do them again. However, we need to consider if the parameters will ever change, how often they will change, and if the changes will be more complicated than the original format. We also need to consider if the list of items will keep growing over time and how much time we have for maintenance on the dashboard. These considerations are essential in ensuring that the calculations are reliable and the dashboard runs efficiently.
In place of IF/THEN statements, I recommend creating a spreadsheet that feeds into the dashboard. While the primary goal of a dashboard should be automation, it’s also important to decrease the need for communication. Therefore, the user should be given a step to do if there are updates. As long as it doesn’t take too much of their time, this is a far better option.
The spreadsheet has all the items that need to be linked located within the same rows. Instead of saying If “Column A” = “Rice” Then “1”, the user simply adds a column with items and a column with numbers. For example, for Rice, they simply put 1. When the data is pulled into Tableau, everything is already linked together, and the auto refresh feature updates the visuals itself. This approach not only saves everyone time but also helps the system run faster.
While this solution doesn’t solve all calculations that require the IF/THEN statement, it will at least solve some. Therefore, it’s essential to understand when to use the IF/THEN statement and when to use other methods, such as a spreadsheet. This can save time, reduce the need for communication, and ensure the calculations are reliable, leading to better insights and more successful data analysis.
