2 Replies Latest reply on May 25, 2016 9:56 AM by Mahfooj Khan

# Calculated field doen't works when one variable is "Null"?

I have a simple table I put together that has 4 columns. (Office, Actuals, Budget, and Variance)

Actuals are just the actuals for the month and Budget is what was budgeted for the month and variance is just a calculated value "Act-Bud".

The formula works when there are values in both the actuals and budget line for a particular office. However some offices did not submit a budget but there ARE actuals hitting against that office. For Example Actuals = \$33K and budget = "", Nothing in budget, just blank. So the variance is showing up as blank... "" instead of 33K for example.

Attached is a screenshot showing the issue. This calculation works perfectly in Excel, why will Tableau not return a value when there is a blank value in one of the variables?

My equation for the "Actual vs Budget Variance" column is: Sum([Actual Financials])-Sum([Budget Financials])

I have a flat file for the financials with a column for all of the financial values and another column for "Version" (i.e. Budget or Actual).

My equation for the Actuals and Budget columns are the following:

Actual Financials

IF [Version]=="Actual"

THEN [Financials]

END

Budget Financials

IF [Version]=="Budget"

THEN [Financials]

END

Tableau View below:

• ###### 1. Re: Calculated field doen't works when one variable is "Null"?

replace using ifnull

calculated field based on dimension attribute value

or use zn to reurn zero for null values