topic Formula - Weekly average for the fist three quarters in a year in Discussions

Formula - Weekly average for the fist three quarters in a year

mstanko — Wed, 31 May 2017 00:05:43 GMT

Hi,

Data is displayed on a daily basis (mon - sat). I have converted the date to days of week, weeks, months, quarters, years.

Iam trying to get the average weekly value for the time period 1/1/2015 - 10/3/2015. This would be displayed on 10/5/2015 and it would contain the same value for the rest of the year.

So in essence, it is one weekly average value for Q1 - Q3.

I think I can do this in a tabulation, however I am looking for a formula on how to do this.

Any help would be greatly appreciated.

Thank you,

Re: Formula - Weekly average for the fist three quarters in a year

mstanko — Wed, 31 May 2017 00:08:16 GMT

I am sorry, I forgot to mention that, one the new year starts, I would have 0 for value until Q4 comes around and then would repeat the process with new sets of data for that year.

THank you

Re: Formula - Weekly average for the fist three quarters in a year

txnelson — Wed, 31 May 2017 03:52:25 GMT

Here is a script that I belive will work......I really didn't test it, but I think you will see the concept. The only guess that I made, was the workweek that Oct. 5th is.

dt = Current Data Table();
tYear = :Year;
If( Date MDY( 1, 1, :Year ) >= :date <= Date MDY( 10, 5, :Year ),
	temp = Col Mean( :Value, :Week, :Year ),
	temp = Mean( :Value[dt << get rows where( :Week == 39 & tYear == :Year )] )
);
temp;

Re: Formula - Weekly average for the fist three quarters in a year

mstanko — Wed, 31 May 2017 18:53:34 GMT

Thank you,

The results were slightly different from what I was looking for (i got each week average vs. average for all the weeks from Q1- Q3), and then I realized that the results produced by the formula is actually exactly what I need. My original question should have been changed.

I appreciate your help with this one.

Thank you very much.

You saved me a lot of time

Re: Formula - Weekly average for the fist three quarters in a year

mstanko — Wed, 31 May 2017 18:56:31 GMT

Hi,

I am new to Jmp and its language. If you don't mind me asking, would you pleae walk me through the formula you listed.

Formula worked and produced what it needed to produce, but I am trying to understand it in english.

Once again, thank you for your help

Re: Formula - Weekly average for the fist three quarters in a year

txnelson — Wed, 31 May 2017 20:30:38 GMT

I suggest that you take each of the functions below, and look them up in the Scripting Index, and study the examples given for each of them

Help==>Scripting Index

Here is my explanation of the formula I passed to you

// Create a pointer to the data table so it can be referenced in the 
// "Get Rows Where" function
dt = Current Data Table();

// The formula is applied to each row of the data table
// setting tYear equal to :Year sets the value of tYear to the 
// Year for the current row.  This is a little trick that allows
// for the "Get Rows Where" function to have something to compare to
tYear = :Year;

// For the current row, check to see if the current rows value of Date
// falls between January 1st and October 5th for the current year
If( Date MDY( 1, 1, :Year ) >= :date <= Date MDY( 10, 5, :Year ),
    // If it is in between those dates then calculate the Mean of
    // the column for Value where all the rows where Week is equal 
    // to the current row's value of week, and where all the rows where
    // Year is equal to the current rows value of year
	temp = Col Mean( :Value, :Week, :Year )
	,
	// If the current row's date isn't between the dates above, then
	// find all of the rows where Week is equal to week 39, and year is 
	// equal to the row;'s current year.  Then find the mean of those values
	temp = Mean( :Value[dt << get rows where( :Week == 39 & tYear == :Year )] )
);

// The value that JMP returns to a formula is the last value exeuted in the formula
// Therefore, by placing the variable "temp" as the last thing seen, the 
// calculated value of temp is what the formula value is
temp;

Re: Formula - Weekly average for the fist three quarters in a year

mstanko — Wed, 31 May 2017 21:32:05 GMT

Absolutely will do.
Thank you for all your help