Daily Average Formula 1

[eao]

I've got a formula to calculate the number of work orders closed on per day (count ({Work_Orders.Work Order #}, {Work_Orders.Close Date})), but I cannot summarize this formula field to get averages, min, max, etc. Is there some other way I should be trying to calculate this data?

 

[synapsevampire]

Do you want daily averages also?

minimum({Work_Orders.Work Order #}, {Work_Orders.Close Date}))

maximum ({Work_Orders.Work Order #}, {Work_Orders.Close Date}))

average ({Work_Orders.Work Order #}, {Work_Orders.Close Date}))

So I'll guess that you mean at another group level or the grand total, which of course you should take the time to include in your post.

min or max are simple, just change or omit the current group level.

If you want the average of the averages, rather than the true average (by just using the average forumla) then you'll want to use variables:

Group Footer Formula:
whileprintingrecords;
numbervar MyWO;
numbervar MyCount;
MyWO:=MyWO + count ({Work_Orders.Work Order #}, {Work_Orders.Close Date}));
numbervar MyCount:=MyCount+1;

Now in the report footer use:

whileprintingrecords;
numbervar MyWO;
numbervar MyCount;
If MyCount > 0 then
MyWO/MyCount
else
0

-k

 

[eao]

I'm not looking for "the average of the average". I am looking for the average of the total number of work orders per day. Your formulas work great to figure out the arithmatic mean, but what about min, max, median, etc? Can these be derived as well?

 

[eao]

When I try the formula below, Crystal tells me "this field cannot be summarized".

minimum (count ({Work_Orders.Work Order #}, {Work_Orders.Close Date}))

And I had to remove a closing parentheis from your Group Footer formula; it was throwing a syntax error.

 

[rosemaryl]

Dear Eric,

You use the knowledge gained from SV's (* from me) helpful post to you, to extrapolate that you need to again use variables.

Also, I recommend that you change your grouping as the Close Date field in that table is a calculated field converted to a varchar (text field). If you do not change your grouping to Close Date & Time ordered by Day, then my formula below will fail with an error on the field name.

Here is an example:
//Placement Group Footer.
WhilePrintingRecords;
numbervar min;
numbervar MyCount;
min := If mycount = 1 then
Count({Incident.Incident #}, {Incident.Close Date & Time}, "weekly")
else if
mycount > 1
then if min < Count({Incident.Incident #}, {Incident.Close Date & Time}, "weekly")
then min else Count({Incident.Incident #}, {Incident.Close Date & Time}, "weekly");
min

//Begin Display Min
//placement report footer
whileprintingrecords;
numbervar min;
min
To get the MAX, you would create a new set of two formula (Group Footer and Report Footer) using a new variable named Max perhaps and check to see if Max is > than your count formula.

regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[rosemaryl]

Dear Eric,

My apologies on the formula being on the wrong view name and grouping... in my testing I was using another view and grouping, you group footer formula should be:

WhilePrintingRecords;
numbervar min;
numbervar MyCount; //originally declared in formula SV gave you
min := If mycount = 1 then
count ({Work_Orders.Work Order #}, Work_Orders.Close Date & Time}, "daily")
else if
mycount > 1
then if min < count ({Work_Orders.Work Order #}, Work_Orders.Close Date & Time}, "daily")
then min else count ({Work_Orders.Work Order #}, Work_Orders.Close Date & Time}, "daily") ;

Regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762