Running Total that includes group value

[kpeeler]

I am using version 8.5. I am trying to create a formula that indicates if a record was closed in <= 9 days after the beginning of the group week date. I am using a group based on received date with the "for each week" option selected. The formula simply establishes a value of 1 if the closed date <= 9 days from group week date and 0 if not. The formula returns the proper values but I can not then sum the total for closed (i.e. = 1) or create a running total (in fact the formula is not even available when creating a running total or through insert summary). Below is the formula but I think I am missing a basic concept of how Crystal utilizes formulas that incorporate values from across records.

whileprintingrecords;
if {@Closed Date} - 9 <= cdate(GroupName ({@Recv Date}, "weekly"))
then 1
else 0

 

[lbass]

Try:

if {@Closed Date} <= cdate({@Recv Date})+ 9
then 1
else 0

This is a guess, since you didn't provide the formulas for {@Closed Date} or {@Recv Date}. It is unlikely that you need "whileprintingrecords". If this still doesn't work, then provide those formulas.

-LB

 

[kpeeler]

LB - Thanks for your reply. The @Recv Date & @Closed Date are as follows:

@recv date:
dtstodatetime({CallLog.RecvdDate})

@closed date:
if trim({CallLog.ClosedDate}) <> ""
then dtstodatetime({CallLog.ClosedDate})
else date(0000,00,00)

I don't think these are the issue. The reason I say this is when I try to build a running total based on:
Field to Summarize: distinct count record#
Evaluate use formula: {@Closed Date} - 9 <= datevalue(GroupName ({@Recv Date}, "weekly"))
On Change of Group: Group#1: @Recv Date -A
I receive the following error when placing in report:
"A running total cannot refer to a print time formula".

It appears that the use of the value from the group (i.e GroupName ({@Recv Date}, "weekly")) is causing the problem.

 

[lbass]

There is no reason for the "whileprintingrecords", so remove that. Nor do you need to use the group value in your formula, since every record within the group will have the same date that you grouped on. So try my formula.

It might be irrelevant, but I would also try to make the original formulas consistent

//@closed date:
if trim({CallLog.ClosedDate}) <> ""
then dtstodatetime({CallLog.ClosedDate})
else datetime(0,0,0,0,0,0)

Then your final formula would be:

if date({@Closed Date}) <= date({@Recv Date})+ 9
then 1
else 0

You should be able to insert summaries on this, or, if you have duplicates, use a running total with a distinctcount of an ID field, with the following formula in the evalution section:

date({@Closed Date}) <= date({@Recv Date})+ 9

-LB

 

[kpeeler]

LB - Again thanks for the response. I think I need to clarify my goal. We must report on all calls that are closed within a given work week, the period of closure not to exceed 2 days from the end of the reporting week. Thus I am using the Recv Date as a group with the "for each week" option and need to use the actual begin date of the group week in the formula (i.e. the close date may not exceed the beginning of the report week date by 9 days). If I was to use just Recv and Close Date in the formula then I am saying that calls can be closed up to 9 days after the receive date of the call.
The formula works:
if {@Closed Date} - 9 <= cdate(GroupName ({@Recv Date}, "weekly"))
then 1
else 0
I just can't insert a summary or create a running total because of report pass rules in Crystal. Even when I play games with passing the group date as a variable (i.e. beginning of week date) I still can't summarize.

 

[lbass]

Sorry, wasn't thinking. Try changing the formula to:

if date({@Closed Date}) <= date({@Recv Date})-dayofweek({@Recv Date})+ 10
then 1
else 0

This will compare the Closed Date to the beginning of the week (+9) that Recv Date falls in. This assumes that your weekly groups are based on weeks starting with Sunday.

-LB

 

[kpeeler]

LB - That works! May all of your children receive full scholarships to Harvard. Many thanks, my day just got a whole lot better.

- Kevin