Determining the Average from a formula that determines the number of d 1

[Bennie47250]

Using Crystal Version 7.0

I have a formula that is determining the number of business days it takes to close a service ticket. Now I would like to get the average number of days for each group.

When I right click on the field, I don’t have the option to insert a subtotal, grand total or a summary.

I then thought will I just create a running total and then do the math. I receive the error message that “You can’t select this field as the field to summarize/reset/evaluate.

The formula I’m using to determine the number of business days is:

//CALCULATE THE NUMBER OF BUSINESS DAYS
//INCLUDING HOLIDAYS IN THE TOTAL
//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//INPUT REQUIRED _ INPUT REQUIRED _ INPUT REQUIRED
//ENTER FIRST DATE
DateVar FirstDay:= {@MinBeginASPendAsDate}; //enter your start / first date here
//ENTER END DATE
DateVar LastDay:= {@MaxEndASCompAsDate}; //enter your end / last date here
//!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
//END INPUT REQUIRED _ END INPUT REQUIRED _ END INPUT
//Other variables used in the formula //DECLARES VARIABLES
Numbervar Days;
Numbervar Weekends;
Numbervar Finaldays;
DateVar StartDate;
DateVar EndDate;
//BEGIN FORMULA:
//*********************************************************
//FINISH FORMULA IF FIRSTDAY OR LASTDAY IS NULL
//********************************************************* //ERROR CHECKING
IF FirstDay <=Date(0,0,0) or LastDay <=Date(0,0,0) then
Finaldays:= 0
//ELSE ASSIGN FINALDAYS
//*********************************************************
ELSE
(
//ASSIGN START DATE
//*********************************************************
//if the first day falls on a weekend, StartDate is equal
//the following Monday for calculation reasons
If DayOfWeek(FirstDay) = 7 Then //IF START DATE IS SATURDAY (7) THEN ADD 2 DAYS TO GET TO MONDAY
StartDate := FirstDay + 2
Else If DayOfWeek(FirstDay) = 1 Then //IF START DATE IS SUNDAY (1) THEN ADD 1 DAYS TO GET TO MONDAY
StartDate := FirstDay + 1
Else
StartDate:=FirstDay; //IF START DATE IS NOT SAT OR SUNDAY THEN USE START DATE
//ASSIGN END DATE
//*********************************************************
//if the last day falls on a weekend, EndDate is equal to
//the following Monday for calculation reasons
If DayOfWeek(LastDay) = 7 Then //IF END DATE IS SATURDAY (1) THEN SUBTRACT 1 DAYS TO GET TO FRIDAY
EndDate := LastDay - 1
Else If DayOfWeek(LastDay) = 1 Then //IF END DATE IS SUNDAY (1) THEN SUBTRACT 2 DAYS TO GET TO FRIDAY
EndDate := LastDay - 2
Else
EndDate := LastDay; //IF END DATE IS NOT SAT OR SUNDAY THEN USE END DATE
//CALCULATE DAYS (including today, First Day and Last Day)
//*********************************************************
Days:= (EndDate - StartDate) ; + 1
//CALCULATE WEEKENDS
//*********************************************************
Weekends:= (EndDate - (dayofweek(EndDate)-1)) - (StartDate
+ (7-dayofweek(StartDate))) +1;
if Weekends = 2 then
Weekends:=Weekends
else if Weekends <=0 then
Weekends:=0
else
Weekends:= (((Weekends-2)/7)*2) + 2;
//CALCULATE FINAL DAYS (DAYS - WEEKENDS)
//*********************************************************
FinalDays:=Days-Weekends;
if FinalDays < 0 then
FinalDays:=0;
);
//DISPLAY NUMBER OF BUSINESS DAYS IN THE RANGE
//*********************************************************
FinalDays;

 

[synapsevampire]

Why not state what this group is "I have a formula that is determining the number of business days it takes to close a service ticket. Now I would like to get the average number of days for each group."?

Determining the average number of days per group, is different from the average number of days across all groups

What you'll probably want to do is create a formula to keep track of the toal days per group.

Group Header section:

whileprintingrecords;
numbervar Groupdays:= 0

Details section:
whileprintingrecords;
Numbervar Finaldays;
numbervar Groupdays:=Groupdays+FinalDays

Group Header section:

whileprintingrecords;
numbervar Groupdays

If you need the average for all groups, move the Group Header section formula to the report header, and display in the report footer.

-k

 

[Bennie47250]

Opps I failed to mention the group as I did not think it would matter.

Our service tickets are issued to accounts like Chevrolet, BMW, Ford, Honda... and I plan on grouping the tickets by the account name and be able to determine the average number of days it takes to close the tickets for that group. An example I have is we issued 284 tickets for Honda and the total number of days it took to close the tickets was 6856. That gives us an average of 24 days

 

[synapsevampire]

Did you try what I'd suggested?

The only

So what you mean is that you have 2 groups, Account and then Ticket.

Create both groups and then use the following for the group footer formula:

I screwed up in the previous post, the last forkmula should be group footer:

Group Footer section:

whileprintingrecords;
numbervar Groupdays;
GroupDays/distinctcount({table.tickets},{table.account})

-k

 

[Bennie47250]

Your correct, I will have 2 groups. One group for the account and another for the ticket.

No I have not tried your suggestion yet. I will and post the results.

 

[Bennie47250]

Synapsevampire:

Thanks this worked like a charm. I suspose this is the 3 formula running total method.

I'm not sure what is happening here, could you explaine?