Formula Help: Calculating months per list of Dates 1

[mtepfer]

I am using CR9, I am taking a date input from a user, with that date I am selecting a list of criteria from a SQL view. The main component is called lease date. So if a client selects June it will pull back all data that was paid in June and those clients starting lease date, which could be anywhere from June backwards in this instant (never forward from the selection date). From this I need to compute the number of leases that are in the current month, then also go back and sum prior months if the lease started before 6/1. Any dates after the first of the month do not count for that month.

I have been able to get formulas that calculate the number of retro months, the starting month on an old lease date, and the sum of the current months, but can't figure out how to total the retro months.

My data is as follows: (assuming 6/1 start)

Lease Dates No. Retro Months Start Month
3/31/05 2 4
6/1/05 0 0
4/20/05 1 5
11/29/04 6 12

THe output should then look as follows:

June 4 (Current month i have working)
May 3
April 2
March 1
Feb 1
Jan 1
Dec 1

I do have it so the months will also print out in order based on the start date entered.

Any suggestions or ideas on how to go about this is greatly appreciated.

 

[Madawc]

What formula are you using to total the 'retro monhts'? Why can't it be used to total them for the whole report?

I suspect you are using summary totals, in which case you need to create a running total for the whole report, using a formula so that it only adds running months.

Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.


Madawc Williams (East Anglia, UK)
Windows XP & Crystal 10

 

[mtepfer]

I'm not totaling using the retro months, my retro formula is:

int(({?Check Run Date} - Day ({?Check Run Date}) + 1 - {vwAdminFeeReport.newlease}) / 30)

I created this in order to help me total the retro months.

I get the current month by counting the number of records brought back from the selection date.

 

[mtepfer]

Ok I've got the following formula to tally each month and store it in a variable.

Dim counter
Dim curmonth
Global Jan As Number
Global Feb As Number
Global March As Number
Global April As Number
Global May As Number
Global June as Number
Global July as Number
Global August as Number
Global Sept as Number
Global Oct as Number
Global Nov as Number
Global Dec as Number

counter = {@No. Retro Months}
curmonth = Month ({?Check Run Date})


Do While counter > 0
If curmonth - counter > 0 then
curmonth = curmonth - counter
else
curmonth = curmonth + counter
end if

counter = counter - 1

Select Case curmonth
case 1
Jan = Jan + 1
case 2
Feb = Feb + 1
case 3
March = March + 1
case 4
April = April + 1
case 5
May = May + 1
case 6
June = June + 1
case 7
July = July + 1
case 8
August = August + 1
case 9
Sept = Sept + 1
case 10
Oct= Oct + 1
case 11
Nov = Nov + 1
case 12
Dec = Dec + 1
End Select
curmonth = Month ({?Check Run Date})
loop

formula = Jan

My question now is how do I output each variable, the formula only returns one (as far as I know) I need each variable to show up in a summary of each month.

 

[lbass]

I think you could create a series of formulas something like:

//{@currentmo}:
if {table.leasedate} < {?date} then 1

//{@currmo-1}:
if {table.leasedate} < dateadd("m",-1,{?date}) then 1

//{@currmo-2}:
if {table.leasedate} < dateadd("m",-2,{?date}) then 1

...etc. Then right click on each formula and insert a sum. I think you need to use dates, not startmonths, to allow for crossing years. Add text boxes for labels in the report footer.

-LB