3 Replies Latest reply on Oct 8, 2018 11:24 PM by philipp.gntzel

# How to remove null or NULL values from table calculations?

Hello altogehter,

currently I want to do something -seemingly- easy.

I want to calculate the year-on-year difference percentage from one to another year.

Now what happens is that some companies have monthly  values for last year but zeros or Null values for this year. Since I want to to the yoy comparison on average per month basis, these values would distort the average and thus the difference percentage.

Thus, I need a filter which searchs for zeros or null values in a month and if there is a zero in one month a year, remove the whole month from the calculation.

How can I achieve this (if possible without LODs)?

Look at example / screenhots :-)

Philipp

• ###### 1. Re: How to remove null or NULL values from table calculations?

Hi Phillipp--

Have you searched the Forums for an answer? This comes up quite a bit, and you may stumble across an existing answer.

I recommend checking this out:

Removing Null

Your options will likely depend on how your data is structured. For example, if every entry has a NULL or 0 value, that's going to be easier to work with, as opposed to instances when the month data simply doesn't exist.

Thanks!

• ###### 2. Re: How to remove null or NULL values from table calculations?

Philipp,

Do you mean this? Since Sales on Feb 2018 is Null, the calculation skip it and leave it blank. If yes, here is the calcs:

Non Zero or Null % Dif:

((AVG(IF [Sales]>0 THEN [Sales] END)) - LOOKUP(ZN(AVG([Sales])), -1)) / ABS(LOOKUP(ZN(AVG([Sales])), -1))

Hope it helps.

Michael Ye

• ###### 3. Re: How to remove null or NULL values from table calculations?

Hello Michael,

thank you very much for your response!

It is kind of the solution. The last step would be to calculate the average year on year so putting the month out...but then unfortunately it doenst work anymore and tableau puts the months back in

Best regards
Philipp