Automatic "Rolling" Quarter Date View Column

Author: Tripp W Black

Created: 04/17/2001 at 06:27 PM


Notes Developer Tips
Formulas, Views

Title: Rolling quarter views
This tip was submitted by David Peabody.

If you need views, based on a date field, for rolling quarters, this
formula will select the documents depending on if you want 1 quarter
ago, 2 quarters ago, etc. You'll have to specify the value for
AdjValue and the form name in the SELECT statement at the end of the


REM"I use this formula for creating views that select documents on a rolling
REM"If you need views for the last 4 Qtrs, you'll need 4 views";
REM"If You want 1 Qtr Ago set AdjValue to 2";
REM"If You want 2 Qtrs Ago set AdjValue to 5";
REM"If You want 3 Qtrs Ago set AdjValue to 8";
REM"If You want 4 Qtrs Ago set AdjValue to 11";
REM"variables to be manipulated";
ADJMonth := ADJMonth;
QtrEnd := QtrEnd;
REM"To determine if the current month begins a quarter, we must do two things";
REM"We will perform modular division by 3 on the current month and in order to
REM"for Jan. and Feb. we'll add 3 to the value of the current month";
date:=@Adjust(@Today; 0; 3; 0; 0; 0; 0);
dayofmonth := @Day(date);
dateMOD3 := @Modulo(newmonth;3) ;
REM"If this month ends a quarter, dateMOD3=0, we need to go back 5, 8, 11, or 14
REM"If this month does not end a quarter, -(dateMOD3) - AdjValue, will give us
the start month of the Qtr";
@If(dateMOD3=0;@Set("ADJMonth";-3-AdjValue);@Set("ADJMonth"; -(XMOD3) -
REM"Set the start month";
desiredQtrStart := @Adjust(@Today;0;ADJMonth;0;0;0;0);
REM"Set the start date";
QtrStart :=
REM"Set the end date";
QrtrStartMonth := @Month(QtrStart);
QrtrEndDate := @Adjust(QtrStart;0;2;0;0;0;0);
REM"Check for months ending with 31 days";


SELECT @If(DateFieldNameOnYourForm>=QtrStart & DateFieldNameOnYourForm
<=QtrEnd; Form = "YourFormName";"")


previous page