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
formula.
CODE:
REM"I use this formula for creating views that select documents on a rolling
quarter";
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";
AdjValue:=2;
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
account";
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);
newmonth:=@Month(date);
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
months";
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) -
AdjValue));
REM"Set the start month";
desiredQtrStart := @Adjust(@Today;0;ADJMonth;0;0;0;0);
REM"Set the start date";
QtrStart :=
@TextToTime(@Text(@Month(desiredQtrStart))+"/01/"+@Text(@Year(desiredQtrStart)))
;
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";
@If(QrtrStartMonth=1|QrtrStartMonth=10;
@Set("QtrEnd";@TextToTime(@Text(@Month(QrtrEndDate))+"/31/"+@Text(@Year(QrtrEndD
ate))));
@Set("QtrEnd";@TextToTime(@Text(@Month(QrtrEndDate))+"/30/"+@Text(@Year(QrtrEndD
ate)))));
SELECT @If(DateFieldNameOnYourForm>=QtrStart & DateFieldNameOnYourForm
<=QtrEnd; Form = "YourFormName";"")
END CODE
previous page
|