Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Averages with Invalid Data Post Reply Post New Topic
Author Message
PAlexander
Newbie
Newbie


Joined: 08 Jan 2008
Online Status: Offline
Posts: 15
Quote PAlexander Replybullet Topic: Averages with Invalid Data
    Posted: 13 Aug 2008 at 1:09pm
I have a database that contains a start date/time and an end date/time.  I need to find the average time spent.  It is possible, due to entry error, that the start date/time is greater than the end date/time.
 
START DATE/TIME END DATE/TIME DIFF.
7/28/2008 17:57 7/28/2008 18:28 31
7/28/2008 19:59 7/28/2008 18:10 -109
7/28/2008 17:57 7/28/2008 18:28 31
7/28/2008 18:08 7/28/2008 18:47 39
7/28/2008 18:11 7/28/2008 18:59 48
  AVERAGE 8

What I would like to do is place text called ** Error ** into the DIFF column if that condition occurs and not include that row in the count used to calculate the average, like in the example below:

(31 + 31 + 39+ 48) / 4 = 37
 
START DATE/TIME END DATE/TIME DIFF.
7/28/2008 17:57 7/28/2008 18:28 31
7/28/2008 19:59 7/28/2008 18:10 ** Error **
7/28/2008 17:57 7/28/2008 18:28 31
7/28/2008 18:08 7/28/2008 18:47 39
7/28/2008 18:11 7/28/2008 18:59 48
  AVERAGE 37
 
Does anyone know how to do this?  I want to completely ignore the row in error so to find my average I would need the Average to divide by 4 instead of 5.
 
Thanks!
"Pete" 
 
 
 

 
IP IP Logged
themessenger
Groupie
Groupie
Avatar

Joined: 15 Aug 2008
Location: United Kingdom
Online Status: Offline
Posts: 48
Quote themessenger Replybullet Posted: 15 Aug 2008 at 3:02am
Create two formula fields

FORMULA1:
If {DIFF} > 0 then {DIFF} else {DIFF}

Use this in the report then derive your average in the group footer.  Now delete the field in the Detail section and put the following field in its place:

FORMULA2:
IF {DIFF} > 0 then ToText({DIFF}) else "** Error **"

That should give you what you want
Managing Director
www.allmymenus.com
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 15 Aug 2008 at 3:06am
Hi,
 
Create the formulas below
 
Frm1
 
datediff('n',{Table_Dates.ST_Date},{Table_Dates.EN_Date}) this will give you
31,-109 etc....
 
place this formula in details section then right click select format field then in common tab select suppress and click x+2 and enter the code below
 
left(cstr(datediff('n',{Table_Dates.ST_Date},{Table_Dates.EN_Date})),1) = '-'
 
 
 
 
Frm2
if left(cstr(datediff('n',{Table_Dates.ST_Date},{Table_Dates.EN_Date})),1) = '-' then
'***Error***'
this will give you error text ,Place this formula above the Frm1 formula in your details section
 
Now for Average.
 
Create Running Total ....
 
Select frm1 as Field to summarize
 
Evaluate
select formula and enter
left(cstr(datediff('n',{Table_Dates.ST_Date},{Table_Dates.EN_Date})),1) <>  '-'
 
Reset
Select Never
 
Place the runningtotal in report footer
 
You will need to adjust the decimal palces hope you know how to do that
select the numeric field then format field and customize
 
 
Output
28/07/2008 17:57:00 28/07/2008 18:28:00   31
28/07/2008 19:59:00 28/07/2008 18:10:00   ***Error***
28/07/2008 17:57:00 28/07/2008 18:28:00   31
28/07/2008 18:08:00 28/07/2008 18:47:00   39
28/07/2008 18:11:00 28/07/2008 18:59:59   48
                                                                                  37
 
Cheers
Rahul
 
 
 
Cheers
Rahul
 
 


Edited by rahulwalawalkar - 15 Aug 2008 at 3:07am
IP IP Logged
PAlexander
Newbie
Newbie


Joined: 08 Jan 2008
Online Status: Offline
Posts: 15
Quote PAlexander Replybullet Posted: 15 Aug 2008 at 5:48am
Thanks.  I'll try out both suggestions.
 
"Pete"
IP IP Logged
PAlexander
Newbie
Newbie


Joined: 08 Jan 2008
Online Status: Offline
Posts: 15
Quote PAlexander Replybullet Posted: 15 Aug 2008 at 7:11am
Rahul:
 
The first two formulas work perfectly and do what I want them to do.  Thank you.
 
I'm confused about the average.  Can you please further explain?
The formula that holds the DateDiff statement is named Time Elapsed in my report.  I am taking an average of Time Elapsed (Avg of @Time Elapsed) and putting that in the group footer.  It is not calculating properly.
 
According to your instructions I should summarize Time Elapsed (frm1)?  What do you mean by Evaluate and Reset?  Where does that go?
 
I'm confused.
 
"Pete"
IP IP Logged
rahulwalawalkar
Senior Member
Senior Member
Avatar

Joined: 08 Jun 2007
Location: United Kingdom
Online Status: Offline
Posts: 731
Quote rahulwalawalkar Replybullet Posted: 15 Aug 2008 at 7:17am
Hi
 
What are you grouping on ?
 
You have to create runningtotal formula ,which is different from formulas,
 
In field explorer you will find Runningtotal Fields node select and create new running total ,in that you will find evaluate and reset.
 
 
 
Now for Average.
 
Create Running Total ....
 
Select   Field to summarize
 
Time Elapsed  formula then
 
Evaluate
select formula radio button and enter
left(cstr(datediff('n',{Table_Dates.ST_Date},{Table_Dates.EN_Date})),1) <>  '-'
 
Reset
Select Never radio button
 
Place the runningtotal in report footer
 
 
cheers
Rahul


Edited by rahulwalawalkar - 15 Aug 2008 at 7:22am
IP IP Logged
PAlexander
Newbie
Newbie


Joined: 08 Jan 2008
Online Status: Offline
Posts: 15
Quote PAlexander Replybullet Posted: 15 Aug 2008 at 10:39am

Got it, but it still doesn't work.  I must be doing something wrong. 

I group on employee ID.
 
EMP # Start End Time Elapsed
100 7/5/2008  8:46:00 AM 7/5/2008  8:54:00 AM 8
100 7/5/2008  9:18:00 AM 7/5/2008  9:29:00 AM 11
100 7/5/2008  9:44:00 AM 7/5/2008  9:50:00 AM 6
100 7/5/2008  9:49:00 AM 7/5/2008  10:01:00 AM 12
100 7/5/2008  10:41:00 AM 7/5/2008  10:48:00 AM 7
100 7/5/2008  12:28:00 PM 7/5/2008  12:34:00 PM 6
100 7/5/2008  1:54:00 PM 7/5/2008  1:59:00 PM 5
100 7/5/2008  2:33:00 PM 7/5/2008  2:37:00 PM 4
100 7/5/2008  2:50:00 PM 7/5/2008  2:54:00 PM 4
Average for Employee 100 7
13.67
If you look at the above, average should be 7, not 13.67 which is what I came up with using your formula.
 
I created a running total called Provider Total
 
Summary:
     Field to Summarize: @ Time Elapsed
     Type of Summary:  Average
Evaluate:
     Use a Formula:
          left(cstr(datediff('n',{DATA.START},{DATA.END})),1) <>  '-'
Reset:
     Never
 
I put Provider Total in the Group 1 Footer
 
What am I doing wrong?
 
"Pete"
 
 
 
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.