5 Replies Latest reply on Jun 24, 2015 12:47 PM by elizabeth.hoffberg.0

# How to exclude null value in Tableau Calculation

Hi Everybody,

I have the Sheet like belows. There is no Sale Data in some Months. for Example: There is no Sale Data for Category A in 2003/02 and 01/2004. When i calculated the Difference in Sale from current Month to last month (right click on Sale==>  Quick table Calculation ==> Differrence), Tableau used "null value" in the calculation.

For example:

Sale of Category A 10 2003/01 is 10. There is no data for Sale of Category A in 2003/ 02. But Tableau still shows me the Defference between Sale in These 2 months as -10.

So how can i exclude the null value in Calculation?. Thanks

• ###### 1. Re: How to exclude null value in Tableau Calculation

By default, Tableau wraps both the prior and the current value in ZN, either or both of these could be removed by customizing the calculation. If a null value is returned as part of this expression the whole thing will evaluate as null.

I've demonstrated this in the attached workbook. Let me know if it helps.

N.

• ###### 2. Re: How to exclude null value in Tableau Calculation

Note I left the original difference calculation in my custom calculation so you can compare the difference between these. To get this I right clicked the table calc, selected edit table calc, then customize. If this isn't quite what you want then you may need to put back one of the zn() functions. ZN is short for zero null btw, meaning it will return a zero when the value inside the parenthesis is null.

N.

• ###### 3. Re: How to exclude null value in Tableau Calculation

hi Mr. Noah,

Thanks. I see it now.

• ###### 4. Re: How to exclude null value in Tableau Calculation

Great! I'm glad I could help.

I tinkered around with the workbook a bit to show these different options, so you can see the impact of each choice a bit more clearly.

N.

• ###### 5. Re: How to exclude null value in Tableau Calculation

Hi Noah,

I tried using your calculation, but it doesn't really apply to what I'm trying to do (and was therefore useless to me).

The difference between my data and Phuviet's is that I have no calculations on mine. I just want it to be blank if there is no data. It's a straight data pull into a table.

Think you can help?

Thanks,

Elizabeth