4 Replies Latest reply on Apr 11, 2018 11:09 AM by Michel Caissie

# Issues with 3 Mo Avg Calculation and variance KPI symbol

I am a Tableau 10.4 Desktop user building a table viz showing current month total, three prior month totals, the variance between the current month total and the prior three month average and a KPI symbol ot indicate which variances are greater than/less by a factor of 1.5.  My current data source is Excel.  Twbx file is attached.

I have two problems:

#1 - I cannot get an average to display if one of the prior months are blank.  Is there a way to do this?

#2 - I need to create a KPI symbol ot display if the Variance % is less than -1.5 or greater than 1.5.  Right now I am getting an error on the calculation I tried.

Thanks so much for your suggestions!

• ###### 1. Re: Issues with 3 Mo Avg Calculation and variance KPI symbol

Hi Teresa,

The first challenge is caused because when a month doesn't have a value it returns NULL. Adding any number to NULL returns NULL. You can change that by converting NULL to 0 when appropriate, for instance:

The second one seems like it's just a syntax error, this works:

The workbook is attached.

Hope that helps!

Santiago

• ###### 2. Re: Issues with 3 Mo Avg Calculation and variance KPI symbol

By the way, you can replace  IFNULL(x,0)  with the shortest   ZN(x).

The Zero Null  function does the same thing;   if null then 0.

• ###### 3. Re: Issues with 3 Mo Avg Calculation and variance KPI symbol

Thanks Santiago and Michel for your suggestions.  I do have another question regarding Dimensions and null values.   Is there a way I can display data for a dimension so that "Null" does not appear in the view?  Perhaps a calculated field?

• ###### 4. Re: Issues with 3 Mo Avg Calculation and variance KPI symbol

You can replace Adjustment Reason with