topic Re: Calculate moving average based on 2 categorical columns and on combined data in Discussions

Calculate moving average based on 2 categorical columns and on combined data

CJenvey — Fri, 09 Jun 2023 19:46:57 GMT

I have a dataset with mean Dozens of eggs per OrderId and OrderDate for Trial (Before, After) and TrialWeek (1, 2, 3, 4, 5). I would like to use the Moving Average function to calculate the mean Dozens at the end of every week, using the data from the previous weeks, and then input this number into a new column. So there should be repeated mean dozens for every row that have the same Trial and TrialWeek.

For example:

Trial == Before & TrialWeek == 1 = Mean Dozens for TrialWeek 1

Trial == Before & TrialWeek == 1 & 2 = Mean Dozens TrialWeek 2

Trial == Before & TrialWeek == 1 & 2 & 3 = Mean Dozens TrialWeek 3, and so on.

Moving Average wants me to input the number of rows that the average is calculated on, however the number of rows per TrialWeek is not the same, so I can't use a fixed number of rows to calculate this. I know that I will need to write a formula to do this, but I'm stuck as to how to compose the formula. I am using JMP Pro 16. Any help would be greatly appreciated!

Re: Calculate moving average based on 2 categorical columns and on combined data

txnelson — Mon, 17 May 2021 05:55:11 GMT

Is this what you are looking for?

x = .;
curWeek = :TrialWeek;
If( :Trial == "Before" & :TrialWeek != :TrialWeek[Row() + 1],
	x = Col Mean( If( :Trial == "Before" & :TrialWeek <= curWeek, :Dozens, . ) )
);
x;

Re: Calculate moving average based on 2 categorical columns and on combined data

CJenvey — Tue, 18 May 2021 04:25:31 GMT

Thank you @txnelson !

I've adjusted the code to also calculate the moving average for Trial == After, so that seems to be working. But I noticed that the code wasn't calculating the moving average for TrialWeek == 1 unless I had the table sorted.

x = .;
curWeek = :TrialWeek;
If(
	:Trial == "Before" & :TrialWeek != :TrialWeek[Row() + 1], x = Col Mean( If( :Trial == "Before" & :TrialWeek <= curWeek, :Dozens, . ) ),
	:Trial == "After" & :TrialWeek != :TrialWeek[Row() + 1], x = Col Mean( If( :Trial == "After" & :TrialWeek <= curWeek, :Dozens, . ) )
);
x;