2 Replies Latest reply on Nov 28, 2018 5:00 PM by Esther Aller

    Apply a calculated field result to Null data

    Troy Brommenschenkel

      Hello All,


      I'm hoping that this is even possible.  What I am attempting to do is take the results of a calculated field within a specific date range and carry those values over and/or apply them to older fields that have null values. The data looks like this currently:

      The values in the bottom row (1.18%, 1.17%, ect.), I would like to take these values from September 2017 to August 2018 and insert them into the Null fields going backward to 2015.  Or if anyone knows an easier way to do this not in Excel (there are 4M rows of data), I am all ears? 


      Thanks in advance!

        • 1. Re: Apply a calculated field result to Null data
          Chris McClellan

          Do you have some dummy data (not 4M rows obviously ) ?


          You can probably do this with a calculated field and a LOOKUP function, but it might depend on the structure of the data.

          • 2. Re: Apply a calculated field result to Null data
            Esther Aller

            Hi Troy,


            There are 3 ways you can have blanks in a Tableau view: 1) the data is entirely missing, 2) there are rows in the underlying data source for those dates but that measure is blank, 3) the data is filtered out of the view.


            Depending on what caused the blanks, and how the final view is structured, different options are available. See Replace NULL or missing data with zeros


            That article talks about replacing the empty value with zeros, but you can also replace it with another value in your view. For example instead of ZN(LOOKUP(...,0)) you could use IFNULL( SUM([Field]), LOOKUP(SUM(Field], -1)) ) which would replace a NULL value with the value of the same field from one partition ago (but if last month was also NULL, then you would be replacing a NULL with a NULL so be careful there)


            The downside with LOOKUP() is that you need to know exactly how many columns back you want to go. If instead, you always wanted to use the last valid value, you could use something like IFNULL( SUM([Field]), PREVIOUS_VALUE(0) ). The PREVIOUS_VALUE() function will return the last value of the calculation it is in.


            Table calculations can do really cool stuff, but are a bit complex. I recommend reading Transform Values with Table Calculations - Tableau for a deeper understanding!


            Hope this helps

            1 of 1 people found this helpful