That worked perfectly
I just want to understand how it works though if possible.
I rarely use lookup functions, let me see if I get this correct.
Without the lookup function the calculation should still prove effective in my head, but it does not. It does not work because a value that does not exist is not the same as saying "the value is null".
So the lookup forces a table calculation. It goes cell by cell and says "this cell exists as a piece of this table, therefore is now a part of the dataset. Because it is now null the ZN makes it '0'. Which now triggers the if statement."
Just want to see if I understood that correctly.
You thought process is very correct. What the formula does is a kind of data padding by adding null values.
ISNULL(), IFNULL() and ZN() only work when there are null values. However, in this case there is no data. So, those functions won't work.
What LOOKUP() does, like you correctly said, is to make Tableau generate a null. Then ZN() or IFNULL() can be applied to those triggered null values.
Jonathan Drummey has written extensively on data densification/padding in his blog.
Hope this helps.