Subtotals by multiple custom date ranges

[JamesonKat]

I was wondering if anyone knows how to set up a formula to subtotal a revenue field by multiple custom date ranges all in one report (i.e. 6/01/2000-5/31/2001, 6/01/2001-05/31/2002, 4/01/2000-4/30/2000, 4/01/2001-4/30/2001, etc.). Right now all I can manage to do is split up the revenue monthly grouped by product budget code.

Thanks!

 

[synapsevampire]

If you just need a sum for all of them:

Create a date type parameter and select:
Allow Multiple Values
Range Values

Then place "{yourtable.yourfield} in {yourparameter}" in the record selection criteria.

If you want them summed separately for each range of values entered, then I have a weak solution.

I'll preface this with suggesting that this type of thing is better performed by a database.

Create a running total for however many ranges you think might exist at maximum, for each, add the column to sum, then place something akin to ths in the Evaluate->Use a Formula area:

//(Example for Running Total #1 field

if count({?some date}) > 0 then
{Orders.Order Date} >= minimum({?some date}[1])
and
{Orders.Order Date} <= maximum({?some date}[1])
else
False

Note that you use Crystal's array convention for the date range array itself (the [1]), and the min and max to select the ranges from within them. So you'd increment the > qty for the if and and the array subscript (the [1]) for each running total field you create.

You could do something similar using a loop and storing each value to a numbervar array, and while this might increase the elegance and get a nod and a wink from some coders, this example should suffice if there aren't so many ranges.

And there's probably a better way...

<shrug>

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[MrBillSC]

Hi JamesonKat,

The way I would approach this would be to treat the date ranges as fiscal years. Create a formula to assign a fiscal year to each record then create a group based on the fiscal year formula field. The formula could like like this:

If DatePart (&quot;M&quot;,{ESAVENDR.START_DATE}) < 06 then
DatePart (&quot;YYYY&quot;,{ESAVENDR.START_DATE}) else
DatePart (&quot;YYYY&quot;,{ESAVENDR.START_DATE}) + 1

The formula looks at month to calculate the fiscal year. If the month is less that 6 (jan/may), then the fiscal year is the year of the date. Else add one to the year of the date (june/dec).

 

[synapsevampire]

I may have overstepped your requirement making the assumption that you required a parameter to obtain these date ranges.

Mr.Bill has a good solution in that you can use a numeric formula and group on it, then subtotal your field by that group. Since your data is for less that a fiscal year, just consider it a time period.

To display the date ranges &quot;description&quot; you can then build a If condition into a formula that displays whatever you need based on the period.

The reason that you use a numeric period is to get the proper order.

I do this sort of thing, only with parameters, allowing the users to select their date ranges and using my previously posted solution.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com