Applying Formula to each month

[IanWaterman]

I have 4 formula which work as required for a proscribed date range.

@Brought Forward
If {calldetails.daccepted} < minimum(Monthtodate) and {calldetails.dcleared} >= minimum(Monthtodate) then 1 else 0

@opened
If {calldetails.daccepted} in Monthtodate then 1 else 0

@Cleared
If {calldetails.dcleared} in Monthtodate then 1 else 0

@Carried Forward
If {calldetails.dcleared} = date(1900,01,01) or {calldetails.dcleared} > maximum(Monthtodate) then 1 else 0

Client now wants this to run over a 12 month period and perform these calculations automatically for each month.

Other than hard coding formula for each month I can not think how I would do this, ie 48 formulae. Results need to be charted at end of report.

Any suggestions

Ian

 

[lbass]

I think you should start out explaining what the chart is supposed to look like, as that will affect the solution.

You can create formulas that will apply regardless of the current date that are not hard coded, but you will still need a lot of them--not sure you can avoid that.

For example, two months ago:

If {calldetails.daccepted} < dateserial(year(currentdate),month(currentdate)-2,1) and
{calldetails.dcleared} >= dateserial(year(currentdate),month(currentdate)-2,1) then
1 else 0

-LB

 

[IanWaterman]

LB

Thanks for your input, but your formula would still require one for each month.

Because the I am double counting some records, ie a record carried over from previous month, will be counted as open previous month, and closed current month.

I have therefore deceided to build a command which unions 4 queries based upon the formulae mentioned.

Ian

 

[lbass]

As I said "you will still need a lot of them". A command is a good idea--I think it will make charting easier, too.

-LB