I was completing a client project last week and received data that needed to be ‘graded’ into different boundaries. Since the list was long and I didn’t want to spend time writing out a long calculation, I thought of how to do it faster. My initial thought was to write it out in excel and copy it through to see if it works..
..and it did!
This saved so much time and, due to it being in excel, I could edit the formula near instantaneously if needed
The method
Imagine we have a data set consisting of exam scores for 200 imaginary students with the structure below. (I’ve used Mockaroo to create this data).
..and we wanted to see how many had these corresponding grades in Tableau;
What I would have usually done is type an entire IF calculation in Tableau (which would exactly like the Calculated Field closer to the end of this post). But, as you can imagine, this is time consuming, manual and if there were more boundaries it would be increasingly frustrating to type out.
So, using Excel – if you create the structure of the IF statement and drag the columns down you’ll have done most of the work.
Then, edit the few columns (as highlighted) to ensure the boundaries are correct. Finally, add and check the first and last lines to ensure the structure of the formula is correct for Tableau.
Now, simply copy the whole thing and paste it into the “Create Calculated Field…” box in Tableau.
The formula is now complete and you can now visualize your data (the imaginary cohort didn’t have the best ‘F’ rate).
As you can see, if you have to classify information and you have an exhaustive list this can help you write / edit long calculations quickly.
Another key bonus of this method is that you can use formulas in excel to change and manipulate data for the calculated field. This enables you to quickly make large, accurate calculations without typing anything in Tableau.
I hope you find this helpful. If you have any tips, comments or suggestions please leave a message below or contact me on Twitter @vikb03
Thank you for reading!
