Rolling Quarter Totals

[U079310]

Need to modify our current formula (see below) which computes quarterly totals. This worked well in 2006 but now that we are in 2007 we would like the 1st qtr value to contain 2007 data and the values for 2nd,3rd, and 4th quarters to contain data from 2006. How can we change the formula below to compute running values ? Thanks much.

Rick

If {?WeekEnding} in Calendar1stQtr and {SOP30200.DOCDATE} in Calendar1stQtr
Then
xxxx
Else If {?WeekEnding} in Calendar2ndQtr and {SOP30200.DOCDATE} in Calendar2ndQtr
Then
xxxx
Else If {?WeekEnding} in Calendar3rdQtr and {SOP30200.DOCDATE} in Calendar3rdQtr
Then
xxxx
Else If {?WeekEnding} in Calendar4thQtr and {SOP30200.DOCDATE} in Calendar4thQtr
Then
xxxx

 

[synapsevampire]

Why wouldn't you just post the formula instead of this pseudo code? Obviously this code doesn't do anything that you need.

The formula doesn't make sense in theory either, why would it check for each quarter in one formula, you need four, plus why always require a parameter?!?!

So, here's some theory to ponder, set a default value for the parameter, if the default isn't selected, base the formula on the parameter, otherwise let the formula determine the last four quarters based on todays date, this way you can readily schedule the report to run and not require humans.

More confustion stems from the fact that you want the first quarter to be the most recent data (2007), then in some unexplained way you miraculously revert back to the second quarter to be the oldest data and progress forward again?!?!

I urge you to stop stating architecture and instead look for guidance at the design phase.

Try something like the following formula in the evaluate use a formula section of a running total:

// 1st quarter - oldest
if {?WeekEnding} = cdate(1970,1,1) then //default date
(
if month(currentdate) in 1 to 3 then
{SOP30200.DOCDATE} >= dateserial(year(currentdate)-1,4,1)
and
{SOP30200.DOCDATE} <= dateserial(year(currentdate)-1,6,30)
else
if month(currentdate) in 4 to 6 then
{SOP30200.DOCDATE} >= dateserial(year(currentdate)-1,7,1)
and
{SOP30200.DOCDATE} <= dateserial(year(currentdate)-1,9,30)
else
if month(currentdate) in 7 to 9 then
{SOP30200.DOCDATE} >= dateserial(year(currentdate)-1,10,1)
and
{SOP30200.DOCDATE} <= dateserial(year(currentdate)-1,12,31)
else
if month(currentdate) in 10 to 12 then
{SOP30200.DOCDATE} >= dateserial(year(currentdate),1,1)
and
{SOP30200.DOCDATE} <= dateserial(year(currentdate),3,31)
)
else
if {?WeekEnding} <> cdate(1970,1,1) then //user date
(
if month({?WeekEnding}) in 1 to 3 then
{SOP30200.DOCDATE} >= dateserial(year({?WeekEnding})-1,4,1)
and
{SOP30200.DOCDATE} <= dateserial(year({?WeekEnding})-1,6,30)
else
if month({?WeekEnding}) in 4 to 6 then
{SOP30200.DOCDATE} >= dateserial(year({?WeekEnding})-1,7,1)
and
{SOP30200.DOCDATE} <= dateserial(year({?WeekEnding})-1,9,30)
else
if month({?WeekEnding}) in 7 to 9 then
{SOP30200.DOCDATE} >= dateserial(year({?WeekEnding})-1,10,1)
and
{SOP30200.DOCDATE} <= dateserial(year({?WeekEnding})-1,12,31)
else
if month({?WeekEnding}) in 10 to 12 then
{SOP30200.DOCDATE} >= dateserial(year({?WeekEnding}),1,1)
and
{SOP30200.DOCDATE} <= dateserial(year({?WeekEnding}),3,31)
)

That should get you close for the first quarter only.

Adjust the dates for each quarter required.

So the user can enter a date to base the formulas on or you can let the report run as it determines the dates.

Hard coding is a BAD idea.

-k

 

[synapsevampire]

I should have also mentioned that you need to limit the data being returned in the record selection formula, as in:

// 1st quarter - oldest
if {?WeekEnding} = cdate(1970,1,1) then //default date
(
if month(currentdate) in 1 to 3 then
{SOP30200.DOCDATE} >= dateserial(year(currentdate)-1,4,1)
and
{SOP30200.DOCDATE} <= dateserial(year(currentdate),3,31)
else
if month(currentdate) in 4 to 6 then
{SOP30200.DOCDATE} >= dateserial(year(currentdate)-1,7,1)
and
{SOP30200.DOCDATE} <= dateserial(year(currentdate),6,30)
else
if month(currentdate) in 7 to 9 then
{SOP30200.DOCDATE} >= dateserial(year(currentdate)-1,10,1)
and
{SOP30200.DOCDATE} <= dateserial(year(currentdate),9,30)
else
if month(currentdate) in 10 to 12 then
{SOP30200.DOCDATE} >= dateserial(year(currentdate),1,1)
and
{SOP30200.DOCDATE} <= dateserial(year(currentdate),12,31)
)
else
if {?WeekEnding} <> cdate(1970,1,1) then //user date
(
if month({?WeekEnding}) in 1 to 3 then
{SOP30200.DOCDATE} >= dateserial(year({?WeekEnding})-1,4,1)
and
{SOP30200.DOCDATE} <= dateserial(year({?WeekEnding}),3,31)
else
if month({?WeekEnding}) in 4 to 6 then
{SOP30200.DOCDATE} >= dateserial(year({?WeekEnding})-1,7,1)
and
{SOP30200.DOCDATE} <= dateserial(year({?WeekEnding}),6,30)
else
if month({?WeekEnding}) in 7 to 9 then
{SOP30200.DOCDATE} >= dateserial(year({?WeekEnding})-1,10,1)
and
{SOP30200.DOCDATE} <= dateserial(year({?WeekEnding}),9,30)
else
if month({?WeekEnding}) in 10 to 12 then
{SOP30200.DOCDATE} >= dateserial(year({?WeekEnding}),1,1)
and
{SOP30200.DOCDATE} <= dateserial(year({?WeekEnding}),12,31)
)

This will limit the rows returned to the report from the database.

-k