Versión español: https://www.theinformationlab.es/2019/10/21/trucos-favoritos-de-la-semana-para-alteryx-y-tableau/
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.
Simply input:
zn([continuous field])
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.
YEAR([Year])
…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.