1 of 1 people found this helpful
I can think of a couple of ways of handling this...one would be to allow NULLS and then when you want to add things together (in Tableau) using ZN()...5 + NULL = NULL but ZN(5)+ZN(NULL) = 5
The other way (as it sounds like these additions might take place in the loading, so wouldn't be able use ZN) is allow the field in as a String...and then create a calculated field in the form
IIF([field] = 'No data available' OR [field] = 'Data not disclosed', NULL, INT([field])) //or use FLOAT([field]) if it's a decimal
You can then use that field for any calculations.
hope this helps, but if this doesn't solve your issue please post back (letting me know why) and we can probably find a way to handle that situation too.
Hi Simon, thanks for the answer, you sort of brought me back on the right track. I had converted the "data not available" values to zero to avoid null. Now I tested to convert to null values as you suggested, which does not negatively affect the charts. Then, using calculated fields, I can create separate charts to show the count of null values (= "data not available"). This will help the users to check for a potential bias.
Thanks and regards, Andreas Birkenmayer