Spread Report Grouping functions return summary values for a variable for the specified Group Date Range. For each function (GAVG, GMAX, GSUM, GFIRST...), the VarNum parameter sets the variable to be used. The easy way to place these functions is to use Locate, Group Summary. Most Grouping Functions have two forms, one for normal data placement and one for use with variables that are setup to Allow Symbols. For example, GAVG places the raw data, GAVGZ places the data along with the <,>, or ND as appropriate.
The Offset and Grouping set which dates the statistic should be calculated for.
- GroupOffset/StartDate
Used with the Grouping parameter to set the dates to calculate the stat for. Sets the number of Groups (set by the Grouping Parameter) to be added to set the dates. See examples below. If this field is a date string (i.e. "07/16/2004") or refers to a cell with a date in it, it is assumed to be the start date to calculate the stat for.
- Grouping/EndDate
Used to set which dates to calculate the stat for. If the Grouping is a date string (i.e. "07/16/2004") or refers to a cell with a date in it, it is assumed to be the end date to calculate the stat for. If the variable is a daily detail variable and the end date is the same as the start date, it assumes the end date to be at 23:59:59 of that day. Valid Settings are:
| "R" |
Report Dates |
| "H" |
Hours |
| "15M" |
15 Minutes, 00:00 to 14:59, 15:00 to 29:59, etc... |
| "HX:Y" |
Partial Days from hour X though hour Y. |
| "M" |
Monthly |
| "Q" |
Quarterly |
| "SA" |
Semi-Annual |
| "S" |
Season (Winter, Spring, Summer, Fall) |
| "Y" |
Yearly |
| "YTD" |
Year to End Date of the report |
| "YTBSD" |
Year to one day prior to Start Date of Report |
| "MAxx" |
Moving xx number of months. IE "MA12" is a 12 month average. |
| "MOVx" |
Moving average for the x amount of days. IE "MOV7" is the average for the last 7 days including today. |
| "Wn" |
Week as defined by n. IE "W7" is Sunday thru Saturday. Click here for a table defining weeks and the corresponding values for n. Only returns results where the end date of the week is in the report date range. |
| "WSn" |
Week as defined by n. IE "WS7" is Sunday thru Saturday. Click here for a table defining weeks and the corresponding values for n. Only returns results where the start date of the week is less than the report end date and the end date is greater than the report start date. |
| "WOn" |
Week as defined by n. IE "WO7" is Sunday thru Saturday. Click here for a table defining weeks and the corresponding values for n. Returns results for any week regardless of the report dates. IE "WO7" with an offset of 53 would return Sunday thru Saturday for one year from the start date of the report even if the report date range is one month. |
| "A" |
All data including 1970 through the end of the current date. |
In the example below, the report dates are set to Jan 1, 2002 to Jan 31,2002:
=GMAX(1,"1/1/2004","01/17/04") Returns the maximum from 1/1/2004 through 1/17/2004 for variable 1.
=GAVG(1,1,”M”) Returns the monthly (“M”) average for the first month (January) of the report.
=GAVG(1,2,”M”) Returns the monthly (“M”) average for the second month, as specified by the offset of 2, (February) of the report.
=GAVG(1,13,”M”) Returns the monthly (“M”) average for January 2003, as specified by the offset of 13.
=GAVG(1,0,”M”) Returns the monthly (“M”) average for December 2001, as specified by the offset of 0.
=GAVG(1,-1,”M”) Returns the monthly (“M”) average for November 2001, as specified by the offset of -1.
=GAVG(1,2,”Y”) Returns the Yearly (“Y”) average for 2003, as specified by the offset of 2.
=GMAX(1,1,"S") Returns the Season ("S") maximum for 12/20/2001 thru 3/20/2002 (Winter)
=GMAX(1,2,"S") Returns the Season ("S") maximum for 3/21/2002 thru 6/19/2002 (Spring)
=GMIN(1,1,"MA12") Returns the 12 month minimum for 2/1/2001 thru 1/31/2002.
=GMIN(1,2,"MA12") Returns the 12 month minimum for 3/1/2001 thru 2/28/2002.
=GMIN(1,0,"MA12") Returns the 12 month minimum for 1/1/2001 thru 12/31/2001.
=GAVG(1,2,"MOV7") Returns a moving 7 day average for variable 1
The Offset and the Grouping can be used to get data in the future and the past.
The following tables shows the offset to use for common tasks:
Monthly Grouping "M"
Yearly Grouping "Y"
Year to before start date "YTBSD"
Year to End Date "YTD"
Wx, WSx, and WOx groupings Examples
See Tip: Get the average of the last x values for an example using a Start Date and End Date.
Download the Misc_Understanding_Grouping.ss3 for further examples. (Click here for download instructions)