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

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

    Travis Buckingham

      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:

      Capture.JPG