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

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:

 Date Inventory Filled Inventory Change 1/1/20 0 0 1/2/20 0 0 1/3/20 0 0 1/4/20 0 0 1/5/20 30 30 30 1/6/20 30 0 1/7/20 30 0 1/8/20 35 35 5 1/9/20 35 0 1/10/20 30 30 -5

But what I get instead is this:

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

• ###### 1. Re: How to create running difference from a table calculation that fills in missing values

Hi Chris -

You meant like this?  (Model attached)

1 of 1 people found this helpful
• ###### 2. Re: How to create running difference from a table calculation that fills in missing values

Ah, yes it is! I just started learning Table Calculations and I must still be a bit confused with them since I thought PREVIOUS_VALUE was just LOOKUP with the offset fixed at -1. Didn't realize I was totally wrong haha.