My Favourite Tableau and Alteryx Tricks of the Week

by Christopher Marland

Versión español:

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.


…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.


Christopher Marland

Fri 10 Jan 2020

Thu 09 Jan 2020

Wed 08 Jan 2020