Delta calculations

[#1]

I have a dataset with hourly observations the last three years og different water meters. 
The issue is that the observations shows the meter stand, and not the water usage. 
I want to calculate/show a delta change for each hour, for each water meter. How do I approach that? 

Some of the analysis I want to make with this dataset is to find if a meter has a negative usage at one point (indicating water running the wrong way) or that a meter has to big a delta indicating a leakage.

My workplace is starting to work on data quality, so besides the above I will also want to look at things like if timestamps are consistent or if there is change in the timestamps (if observations for a specific meter is not comming back at the same minut-number each hour). 
One analysis tool I am looking forward to explore is the process quality 

[#2]

You can use R with JMP, but that solution looks fairly similar to the solution using Lag() in JMP (you could also use Dif()). My solution with Col Moving Average is more difficult to understand but the idea is to use Col Moving Average to get the value of previous row.

Scripting Guide > Extending JMP > Work with R 

JSL Syntax Reference > JSL Functions, Operators, and Messages > R Integration Functions 

[#3]

You can also use Col Moving Average() to get the value from previous row (can make it easier when working with group columns)

Names Default To Here(1);

// sample data
dt = New Table("Untitled",
	Add Rows(6),
	Compress File When Saved(1),
	New Column("Meter", Character, "Nominal", Set Values({"A", "A", "A", "B", "B", "B"})),
	New Column("Time", Numeric, "Continuous", Format("Best", 12), Set Values([1, 2, 3, 1, 2, 3])),
	New Column("Value", Numeric, "Continuous", Format("Best", 12), Set Values([1, 0, 2, 0, 1, 2]))
);
// Data should be sorted by time


dt << New Column("D", Numeric, Continuous, Formula(
	:Value - (Col Moving Average(:Value, 1, 1, 0, Meter) * 2 - :Value)
	// 2 * :Value - (Col Moving Average(:Value, 1, 1, 0, Meter) * 2
));

[#4]

cool!

much more robust and compact

[#5]

When I try to use any of the solutions here, I only get the first date (=0) and not the rest -I tried to sort in different ways to see if it is here I am doing something wrong

[#6]

Your column names seem to be wrong, start by fixing those in the formula

[#7]

Ah, I see the spelling error.

[#8]

Is there a description of the col moving average function? 
When I look in the support page the description dos not say anything about how many values it takes.

Col Moving Average

Returns the moving average over a given interval based at the current row. Missing values are ignored

[#9]

I will try to upload a new version of the Normalization GUI .

The new version has an option to calculate the difference to the previous row - based on the trick that @jthi provided here.
Jarmo's code (via Col Moving Average) is much better than what you get via the right click new formula column menu

After the update, it will look like this.
New:  "N-1"

[#10]

I did provide link to the documentation in one of my posts: JSL Syntax Reference > JSL Functions, Operators, and Messages > Statistical Functions - Col Moving A... . In my opinion the documentation for Col Moving Average isn't very clear and usually I have to make some small tests to get it working properly (and remember how it works).

Other place to find some documentation (sometimes different...) is Scripting Index from JMP's help menu