cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Register for our Discovery Summit 2024 conference, Oct. 21-24, where you’ll learn, connect, and be inspired.
%3CLINGO-SUB%20id%3D%22lingo-sub-87647%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3EMoving%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-87647%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3EI%20have%20a%20table%20called%20drugs%20with%20columns%20drug%20and%20result%2C%20how%20do%20I%20calculate%20the%20moving%20average%20of%20the%20result%20column%20by%20taking%20the%20first%204%20rows%20followed%20by%20the%20next%204%20rows%20and%20similarly%20continue.%20Pfa%20the%20sample%20table%20for%20reference.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-532174%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ERe%3A%20Moving%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-532174%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3EJMP's%20support%20documentation%20gives%20some%20additional%20background%20for%20how%20the%20formula%20may%20be%20defined%20(defines%20the%20arguments%20and%20also%20includes%20some%20nice%20little%20examples)%3A%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.jmp.com%2Fsupport%2Fhelp%2Fen%2F16.2%2F%23page%2Fjmp%2Fstatistical-functions-2.shtml%23%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.jmp.com%2Fsupport%2Fhelp%2Fen%2F16.2%2F%23page%2Fjmp%2Fstatistical-functions-2.shtml%23%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PatrickGiuliano_0-1660093800183.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22PatrickGiuliano_0-1660093800183.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22PatrickGiuliano_0-1660093800183.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Fcommunity.jmp.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F44691i32DEE1055F339075%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22PatrickGiuliano_0-1660093800183.png%22%20alt%3D%22PatrickGiuliano_0-1660093800183.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20find%20in%20Wikipedia%20(%3CA%20href%3D%22https%3A%2F%2Fen.wikipedia.org%2Fwiki%2FMoving_average%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fen.wikipedia.org%2Fwiki%2FMoving_average)%3C%2FA%3E%26nbsp%3Bmore%20on%20first%20principles%20and%20mathematical%2F%20conceptual%20understanding%20behind%20the%20Moving%20Average%20Calculation%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-105084%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ERe%3A%20Moving%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-105084%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3EThanks%20this%20worked%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-105083%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ERe%3A%20Moving%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-105083%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3Ewhat%20does%20weighting%20%3D%201%2C%20before%20%3D%203%2C%20%3ADrug%20mean%3F%3CBR%20%2F%3ECould%20you%20please%20explain%20the%20formula%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-105082%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ERe%3A%20Moving%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-105082%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3Ethis%20did%20not%20work%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-88024%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ERe%3A%20Moving%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-88024%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Moving%20average%20dialog.png%22%20style%3D%22width%3A%20373px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Moving%20average%20dialog.png%22%20style%3D%22width%3A%20373px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22Moving%20average%20dialog.png%22%20style%3D%22width%3A%20373px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Fcommunity.jmp.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F15002i2532FCBB9DB58BDE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Moving%20average%20dialog.png%22%20alt%3D%22Moving%20average%20dialog.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EI%20think%20this%20will%20give%20you%20the%20moving%20average%20that%20you%20want%20(4%20rows%20before%20and%204%20rows%20after).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-87746%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ERe%3A%20Moving%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-87746%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3EThe%20formula%20to%20use%20is%3C%2FP%3E%0A%3CPRE%3E%3CCODE%20class%3D%22%20language-jsl%22%3EIf(%20Mod(%20Row()%2C%204%20)%20%3D%3D%200%2C%0A%20Mean(%20%3AResult%5BIndex(%20Row()%20-%203%2C%20Row()%20)%5D%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EI%20have%20attached%20your%20example%20data%20table%2C%20with%20a%20new%20column%20with%20the%20formula%20in%20it%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22drugs.PNG%22%20style%3D%22width%3A%20512px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22drugs.PNG%22%20style%3D%22width%3A%20512px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22drugs.PNG%22%20style%3D%22width%3A%20512px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Fcommunity.jmp.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F15001i63F40F710CA32DDB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22drugs.PNG%22%20alt%3D%22drugs.PNG%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-87744%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ERe%3A%20Moving%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-87744%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3EI%20want%20to%20generate%20the%20moving%20average%20column%20as%20shown%20in%20the%20attached%20table%2C%20for%20every%204%20rows%20I%20need%20to%20calculate%20the%20average%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-87743%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ERe%3A%20Moving%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-87743%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3EDo%20you%20have%204%20columns%20of%20results%2C%20or%204%20times%20as%20many%20rows%20as%20you%20show%20in%20your%20sample%20data%20table%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-87741%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ERe%3A%20Moving%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-87741%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ECould%20you%20please%20explain%20the%20next%20step%20after%20this%2C%20I%20want%20to%20calculate%20the%20average%20of%204%20consecutive%20samples%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-87662%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ERe%3A%20Moving%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-87662%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3ECreate%20a%20new%20column%20in%20your%20data%20table%2C%20and%20apply%20the%20folloing%20formula%3C%2FP%3E%0A%3CPRE%3E%3CCODE%20class%3D%22%20language-jsl%22%3ECol%20Moving%20Average(%20%3AResult%2C%20weighting%20%3D%201%2C%20before%20%3D%203%2C%20%3ADrug%20)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-87661%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3ERe%3A%20Moving%20Average%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-87661%22%20slang%3D%22en-US%22%20mode%3D%22NONE%22%3E%3CP%3Ehello%20%2C%3C%2FP%3E%3CP%3Eyou%20can%20try%20with%20Formula%20column%20--%26gt%3B%20row%20--%26gt%3B%20moving%20average%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22moving.jpg%22%20style%3D%22width%3A%20908px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22moving.jpg%22%20style%3D%22width%3A%20908px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22moving.jpg%22%20style%3D%22width%3A%20908px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Fcommunity.jmp.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F14982i470944843464D147%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22moving.jpg%22%20alt%3D%22moving.jpg%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Choose Language Hide Translation Bar
jojmp
Level III

Moving Average

I have a table called drugs with columns drug and result, how do I calculate the moving average of the result column by taking the first 4 rows followed by the next 4 rows and similarly continue. Pfa the sample table for reference. 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Moving Average

The formula to use is

If( Mod( Row(), 4 ) == 0,
	Mean( :Result[Index( Row() - 3, Row() )] )
)

I have attached your example data table, with a new column with the formula in it

drugs.PNG

Jim

View solution in original post

11 REPLIES 11
gianpaolo
Level IV

Re: Moving Average

hello ,

you can try with Formula column --> row --> moving average

 

moving.jpg

Gianpaolo Polsinelli
jojmp
Level III

Re: Moving Average

Could you please explain the next step after this, I want to calculate the average of 4 consecutive samples
txnelson
Super User

Re: Moving Average

Do you have 4 columns of results, or 4 times as many rows as you show in your sample data table?
Jim
jojmp
Level III

Re: Moving Average

I want to generate the moving average column as shown in the attached table, for every 4 rows I need to calculate the average

txnelson
Super User

Re: Moving Average

The formula to use is

If( Mod( Row(), 4 ) == 0,
	Mean( :Result[Index( Row() - 3, Row() )] )
)

I have attached your example data table, with a new column with the formula in it

drugs.PNG

Jim
jojmp
Level III

Re: Moving Average

Thanks this worked
Phil_Kay
Staff

Re: Moving Average

Moving average dialog.png

I think this will give you the moving average that you want (4 rows before and 4 rows after).

jojmp
Level III

Re: Moving Average

this did not work
txnelson
Super User

Re: Moving Average

Create a new column in your data table, and apply the folloing formula

Col Moving Average( :Result, weighting = 1, before = 3, :Drug )
Jim