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 could sort the data by meter and time and then use the lag function (lag (:column, 1) = value of the previous row) to calculate the consumption:

new table("test",add rows(100),
new column ("meter", Nominal, Set Each value (Step(row(),10*(0::11)+1,(0::11)))),
new column ("time",Format( "m/d/y h:m"), set each value(Date DMY( 17, 9, 2023 )+mod(row()-1,10)*3600)),
new column ("reading"));

new column("consumption",  set each value(if(row()==1 |:meter != lag(:meter,1),0,random uniform(0,10))));
for each row( if(row()==1 |meter != lag(:meter,1), :reading =random uniform(0,10000),: reading = lag(:reading,1)+ :consumption));


new column("consumption-calc",Formula( if(row()==1 |:meter != lag(:meter,1),0, :reading - lag(:reading,1))));

[#3]

Thanks. I can try it tomorrow. 
But will this be able to differentiate between each unique meter?
(I have 22 at the moment, scaling to several thousands later) .  
I have the rows: MeterNumber; Datetime; CounterValue;

Exported data from the metering software is by default ordered by datetime (2023-09-17 13:09)

[#4]

Fear not, 

if(:meter != lag(:meter,1)

will make sure of that - but you have to sort the data accordingly:

With meter on the left, select the columns for meter and time and then:

[#5]

To facilitate the task:

This is definitely a case that should be included in the Normalization GUI 

[#6]

A build in function that calculates delta within groups would be good. 
I got it to work but I do have difficult understanding the scripting language, and I tried drag/drop making the formula from scratch but I simply cant get all the parts placed correctly as they are in the script you gave me here

[#7]

There is a way to do this interactively. Right click on your grouping column and make it Group By column for new formulas (data must be sorted by groups and most likely by time or something similar)

Next right click on your calculation/value column and go to Row->Difference

With this you get a formula like

[#8]

I do not know what the input file type is, but if it is and Excel file, or a .csv file your DateTime values should be read into JMP as JMP Date/Time values.  If not, you should take the time to convert them.

Below is a simple example that might give you a leg up on calculating the usage data

Names Default To Here( 1 );
dt = Current Data Table();

// Sort the data if not already sorted
dt << sort( by( :MeterNumber, :DateTime ), Replace table( 1 ) );

// Create the new Usage column
dt << New Column( "Usage",
	formula(
		//As Constant( Usage = 0 );
		If( :MeterNumber != Lag( :MeterNumber ),
			usage = 0,
			:Usage = :CounterValue - Lag( :CounterValue )
		);
	)
);

[#9]

Good advice. I will start my day tomorrow spending some time with datetime formatting.

I have a bit difficult getting the syntax for your example and the previous one.
I am hoping I can use R /dplyr library in JMP? I think I can write something that does the trick

[#10]

I have this R code that should do the trick.


library(dplyr)

your_data <- your_data %>%
arrange(MeterName, DateTime) %>%
group_by(MeterName) %>%
mutate(DeltaMeterValue = MeterValue - lag(MeterValue, default = first(MeterValue))) %>%
ungroup()