This week I learnt a lot of basic tips and tricks with Tableau and Alteyrx. Often when you’re learning something by yourself, you miss a lot of the easy tricks that can make your life a lot easier. For the sake of helping those who, like me, might have missed some I wanted to share some of my favourites:
1 – The ‘ZN’ Calculated Field: How to turn NULLs into 0s.
Too often when you get to using data in Tableau, especially when you’ve already created a few calculated fields, you end up with a lot of NULL values that are incredibly unhelpful. Sometimes you don’t want NULLs and you want 0s.
For that purpose, we have the ‘ZN’ function.
And all those pesky NULLs will be 0s. If you need any help understanding what I mean by ‘continuous field’ I cannot recommend enough this amazing blog written by my colleague Alice.
WARNING: before using this trick, you really should consider whether it’s appropriate for your NULLs to be 0s – sometimes NULLs should be NULLs, it depends on your data and your analysis on whether that’s the case or not.
2 – Right and Left Formulas in Alteryx: How to Rearrange Data.
Sometimes you have data in a field that’s right, but also very wrong. For example, in some trade data I’m working on, for some random reason a few dates were the wrong way around. Instead of being yyMM they were MMyy.
My column was, appropriately, called “Date (Sort Me)” and I solved this issue by filtering out the incorrect data then by using three formulas:
1 – New Column: Year
Right([Date (Sort Me)], 2) – the ‘2’ bit indicates that I just wanted the two digits on the right
2 – New Column: Month
Left([Date (Sort Me)], 2)
3 – New Column: Month + Year
[Month] + [Year]
I then unioned the corrected data back in.
3 – YEAR Function in Tableau: Making Years just….well, years!
Often in Tableau any sort of date data tends to expand into unnecessary complexity 2018 must be 01/01/2018 when you convert it into date data.
What I didn’t know is you can simply be rid of all this unnecessary complexity by using a YEAR function.
…et voilà! Your year is now just a year.
4 – Alteryx ToNumber Formula When You Cannot Change ‘Data Type’
Sometimes the end-product of a formula in Alteryx should be a number, however, Alteyrx can disagree and greys out that helpful ‘Data Type’ button at the bottom of your formula.
I had this issue when I wanted to create some numbers, and I found out that the simple answer is to enclose your entire formula within a ToNumber function, as such:
ToNumber( – insert formula here -)
Super simple, super helpful!
If you encounter any of these issues, I really hope that this might be of some help.