Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Formula - Weekly average for the fist three quar...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 30, 2017 5:05 PM
(540 views)

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,

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 30, 2017 8:52 PM
(1038 views)

Solution

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;
```

Jim

6 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 30, 2017 5:08 PM
(538 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 30, 2017 8:52 PM
(1039 views)

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;
```

Jim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 31, 2017 11:53 AM
(508 views)

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 31, 2017 1:30 PM
(501 views)

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;
```

Jim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 31, 2017 2:32 PM
(496 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 31, 2017 11:56 AM
(507 views)

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