Print Page | Close Window

DateDiff and Average of Formula Field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20299
Printed Date: 03 May 2024 at 6:41am


Topic: DateDiff and Average of Formula Field
Posted By: adavis
Subject: DateDiff and Average of Formula Field
Date Posted: 18 Dec 2013 at 8:08am
I have created a report to calculate how many days on average work orders are open by crew.

I have grouped the report by work crew and in the details section included the requisite information, as well as a formula field to count the number of days each work order was/is open (including those still open).

@WODaysOpen=
if isnull({WKORDER.WO_END_DT}) then
(DateDiff ("d",{WKORDER.WO_STRT_DT} ,CurrentDate ))
else (DateDiff ("d",{WKORDER.WO_STRT_DT} ,{WKORDER.WO_END_DT} ))


In order to get the average, I created a running total field based on this formula field
Running Total Name: AvDaysOpen
Field to smarize: @WODaysOpen
Type: Average
Eval: for each record
Reset: on group

I then placed the running total field in the group footer and Presto! it seems to have given me the average number of days a work order remains open per work crew.

To get an overall average for all crews, I created another formula field
@AverageOpen =
Average ({@WODaysOpen})

My concern is that I can't tell if my logic is correct and there are thousands of work orders, so I'm disinclined to manually verify the output.

I hate to ask everyone, but I really want to make sure I'm tackling this correctly before proceeding. Does this method seem sound to you? I'm still learning CR, and reading threads in this forum has provided so much help.


Replies:
Posted By: DBlank
Date Posted: 18 Dec 2013 at 8:20am
It really depends on what you (or the report request) want.
Do you want an average of all rows (which is what you currently have) or do you want an average of the averages (would require shared variables)?

Posted By: adavis
Date Posted: 18 Dec 2013 at 8:25am
I believe an average for all rows is what the report requester would like to see.

Posted By: DBlank
Date Posted: 18 Dec 2013 at 8:33am
You seem to be on the right track/fine.
since your group averages are not fancy you can also just have used the insert summary function (Blue sigma button) to get both of the values you wanted.
use the insert summary function button twice.
Field to summarize = @Days formula field,
set the calculate this summary = average.
for the group, summary location= select the group.
for the report tota,l summary location= select the Grand total/report footer
 
However what you did with the RT and the Formula field do the same thing.
 

Posted By: adavis
Date Posted: 18 Dec 2013 at 8:37am
Good to know there is a simpler way as well. Thank you for the input!


Print Page | Close Window