2 Replies Latest reply on Mar 27, 2020 7:37 AM by Christopher Nguyen

    How to create running difference from a table calculation that fills in missing values

    Christopher Nguyen

      I have a set of data that has missing values for some dates in it. I can assume that the values stay constant until I get a new value, after which I can continue to use that value until another new one appears. I know that I can fill that in the table using a simple IF statement combined with the ZN() and PREVIOUS_VALUE() functions for say, some inventory values like so:

       

      IF NOT ISNULL(ATTR([Inventory]))
      THEN ATTR([Inventory])
      ELSE ZN(PREVIOUS_VALUE(ATTR([Inventory])))
      END

       

      What I want to be able to do is find a running difference of these filled in inventory numbers to see when, if ever, the inventory goes down. However, I can't seem to subtract the previous value from the current value correctly in the table calculation I created. Basically, I want this:

       

       

      DateInventoryFilled InventoryChange
      1/1/2000
      1/2/2000
      1/3/2000
      1/4/2000
      1/5/20303030
      1/6/20300
      1/7/20300
      1/8/2035355
      1/9/20350
      1/10/203030-5

       

      Filled Inventory.png

      Change.png

       

      But what I get instead is this:

      Screen Shot 2020-03-26 at 6.20.36 PM.png

       

      My workbook is attached. How can I get the chart I am aiming for in Tableau?