Solved

Crystal Report Average "Time in Program"

Posted on 2009-07-06
21
690 Views
Last Modified: 2012-05-07
Hello,
I have a report that includes a "Time in Program" total using the formula below:

Local DateTimeVar dstart := {collection_date.start_date};
Local DateTimeVar dend:= {@isnull};
Local StringVar yy := '';
Local StringVar mm := '';
Local StringVar dd := '';

// years
if DateAdd ('yyyy', -(DateDiff ('yyyy',dstart ,dend )), dend) >= dstart
    then yy := totext(DateDiff ('yyyy',dstart ,dend ),0)
    else yy := totext(DateDiff ('yyyy',dstart ,dend )-1,0)
    ;

// months
if month(dend) = month(dstart)          // same month
    then (


if day(dend) >= day(dstart)
            then mm := '0'
            else mm := '11';


)
    else                                // diff month
        (if DateAdd ('m', -(DateDiff ('m',dstart ,dend )), dend) >= dstart
            then mm := totext(remainder(DateDiff ('m',dstart ,dend ),12),0)
            else mm := totext(remainder(DateDiff ('m',dstart ,dend ),12)-1,0);)
   ;

// days
if day(dend) < day(dstart)          //earlier
    then dd := totext(datediff('d'
                        ,dateserial(year(DateAdd ('m', -1, dend))
                                ,month(DateAdd ('m', -1, dend))
                                ,day(dstart))
                        ,dend),0);
if day(dend) = day(dstart)          //same
    then dd := '0';
if day(dend) > day(dstart)          //later
    then dd := totext(day(dend) - day(dstart),0);

// print out with spacing - can be right or left justified
iif (len(yy) = 1, '  ', '')
& yy & 'y'
& iif (len(mm) = 1, '   ', ' ')
& mm & 'm'
& iif (len(dd) = 1, '   ', ' ')
& dd & 'd'

I was told to use this formula to calculate the years, months and days that a person was in a group.  I need to create a formula to average the "Time in Program" for each program. I currently have the report grouped by:
G1-State, G2-(parent) Program, G3-Program then G4-consumer.

I would like to have an average in G3, G2 and G1

Thanks
0
Comment
Question by:ReportsupportAbility
  • 11
  • 6
  • 2
  • +1
21 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 24788537
Create a subreoprt that calculates total time for each consumer and store that value in a shared variable. Use the shared variable to calculate average with subtotals in each group.
Do not forget to reset the variable value after.
P.
0
 

Author Comment

by:ReportsupportAbility
ID: 24788564
I'm not that familiar with these types of formulas. Can you elaborate on how to store the value in a shared variable and resetting it, please?

Step by step would be very helpful.
thanks!
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24788749
 Can you post the screenshot of the layout of your report? Preferable one for both the "Layout" and "Preview Tab"?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24790721
Do you get the correct values for the time in the group?

mlmcc
0
 

Author Comment

by:ReportsupportAbility
ID: 24813542
yes
0
 

Author Comment

by:ReportsupportAbility
ID: 24813718
Here are the screen shots of the detail and preview screens:


details.bmp
preview.bmp
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 24814964
You will need to modify the formula to do the addition of the time and count the records
Then a new formula to calculate the average.

mlmcc
0
 

Author Comment

by:ReportsupportAbility
ID: 24815082
Can you explain what you mean in detail please, maybe give me and example?
Thanks
0
 
LVL 34

Expert Comment

by:James0628
ID: 24828629
First of all, you appear to have an issue with your dates.  Your preview screenshot shows a couple of lines with 109 years in a program ,which seems unlikely.  :-)  The start date on those two lines shows 00 for the year, so the obvious guess is that the year should be 2000, but it's either actually 1900 instead, or it is being interpreted as such somewhere along the line.  That might be something that you need to look at.  Keep in mind that that inflated year count will affect your averages.

 As for your averages:

 The formula that you posted gets the ending date from a formula named {@isnull}.  Can you post that formula?

 The formula that you posted gets the starting date from a field ({collection_date.start_date}), but it looks like you have that formula in a group header (GH4), meaning that it's not evaluated for each record.  Is that correct?  If the total "time in program" is not a total of every record, that will affect how you calculate the total and how you produce the average (dividing a total from every record by a count of every record would not produce the correct results).

 James
0
 

Author Comment

by:ReportsupportAbility
ID: 24839912
Here is the isnull formula

if isNULL({collection_date.end_date})
    then {?ToDate}

else if {collection_date.end_date} > {?ToDate}
    then {?ToDate}

else if {collection_date.end_date}< {?ToDate}
    then {collection_date.end_date}

else if {collection_date.end_date} = {?ToDate}
    then {?ToDate}

else {?ToDate}
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:ReportsupportAbility
ID: 24839991
I looked in our database and see that the count of years is correct and that in the database is where the error is.  It is a simple user error.  Input is year 1900 that will be fixed
0
 

Author Comment

by:ReportsupportAbility
ID: 24840056
AS far as the evaluation for each record, it is, it's evaluating each record for the consumer who is Group 4, I suppressed that feild for privacy reasons.
0
 
LVL 34

Expert Comment

by:James0628
ID: 24847872
The formula is @DetailTimeProgram, correct?  You have that formula in GH4, which means that it will be evaluated once at the start of each new group 4, _not_ for every record.  If that's correct, then the count that's used to calculate the average also needs to be a count of the group 4's, as opposed to a count of each record.  If every record also happens to be a different group 4, then it's the same thing, but that wouldn't normally be the case.

 James
0
 
LVL 34

Expert Comment

by:James0628
ID: 24848051
Two things I forgot:

 The @isnull formula seems straightforward enough.  I don't think it will be an issue.  I just wanted to see what it was doing.


 How do you want the average to be displayed?  You're showing the "time in program" as a number of years, months and days.  Do you want the average in a similar form?  I'm thinking that that might be a bit tricky to do accurately.  Let's say that you get the difference between the dates in days and calculate an average of that.  How would you convert that to a number of years, months and days?  What's an "average" month?  30.5 days?

 James
0
 

Author Comment

by:ReportsupportAbility
ID: 24849278
I don't know what you mean "each record" If I'm evaluating each consumers time in a group, then isn't that for each consumers record?

I would want the average to be in the same format that it is for time in program and average 3 of years, months, days.

This is the way the old report was that I'm redueing in the crystal format instead of sql.

I hope this can be done becuase this is what was requested.
Thanks!
0
 

Author Comment

by:ReportsupportAbility
ID: 24849299
I meant to say an average # of years, months and days-sorry
0
 
LVL 34

Expert Comment

by:James0628
ID: 24849540
In CR, "records" refers to the individual records that CR reads from the datasource.  For example, if a report is reading invoice items (one record for each item on each invoice) and the report is grouped by invoice number, the invoice number would be the group and the items would be the records.  A formula that was placed in the invoice number group header would be evaluated once for each invoice, at the start of each new invoice.  A formula that was placed in the detail section would be evaluated once for each item on each invoice.

 Your formula is in GH4, so it's evaluated once for each "group 4", whatever group 4 is.  If the formula was in a detail section, it would be evaluated for every record within each group 4.  Which is correct really depends on your data.

 If the formula stays in GH4, we need to get a count of the "group 4"s, to calculate the average.
 If the formula is moved to a detail section, we need a count of the detail records, to calculate the average.

 James
0
 

Author Comment

by:ReportsupportAbility
ID: 24849637
Ok, that's what I thought you were saying, yes that's correct then becuase Group 4 is The Consumer or Person.

So we would need to get a count of people in Group 4

Than use it in the average count of time in preogram for groups 3, 2 and 1

The reason I have the Person in Group four is because for some reason I am getting duplicates when it's in the detail section.
0
 
LVL 34

Accepted Solution

by:
James0628 earned 300 total points
ID: 24856369
The duplicates are because of something in the tables that you're using in the report.  For example, there could be two tables that should have a one to one correspondence (one record in table B for each record in table A), but if the proper fields are not used to link them, additional matches are found.  Or there could just be a table that has multiple records for each person (like an invoice item table with multiple items for each invoice).

 If the report looks OK otherwise, I'd have to guess that you just have multiple records per person.  But if you really don't think that you should have multiple records per person, you might want to double check the tables that you're using and how they're linked.  Also, if the tables that you're using just happen to pull in multiple records for each person and you don't actually need anything from all of those records, but there's no easy way to get rid of them using the links or a record selection, you could go to File > "Report Options" and check the "Select Distinct Records" option.  Under the right circumstances, that can be an easy way to eliminate duplicates.  Just make sure that it doesn't eliminate any records that you actually need.

 Anyway ...

 Create a formula like the following (call it whatever you like):

DateDiff ("d", {collection_date.start_date}, {@isnull})

 All that does is output the number of days between your dates.

 Expand your detail section temporarily and drop that formula in there.  Right-click on that formula and select Insert > "Running Total".  Change the running total name if you like (this is going to be the total number of days in program for group 1).  The "Type of summary" is Sum.  Under Evaluate, select "On change of group" and select your group 4 from the dropdown list.  That means that the result of that formula will only be added once for each group 4 (each person), rather than once for each record read for each person.  Under Reset, select "On change of group" and select your group 1.  That means that this running total will give you a total for each group 1.  When you click OK, CR will put a running total field on the report.  Move it to GF1, if it's not already there.

 That should give you a total of the number of days in program in group 1.

 Right-click the formula in the detail section again and select Insert > "Running Total" again.  Change the running total name if you like (this is going to be a count of the people in group 1).  The "Type of summary" is Count (Not "Distinct Count".  Just Count).  The rest is just like before.  Under Evaluate, select "On change of group" and select your group 4 from the dropdown list.  Under Reset, select "On change of group" and select your group 1.  When you click OK, CR will put a running total field on the report.  Move it to GF1, if it's not already there.

 That should give you a count of the people (group 4's) in group 1.

 To get your average, expressed as years, months and days, create a formula like the following (call it whatever you like) and put it in GF1.

Local NumberVar avg;
Local NumberVar yr;
Local NumberVar mh;
Local NumberVar dy;

avg := {#first running total above} / {#second running total above};

yr := Truncate (avg / 365.25);
mh := Truncate ((avg - (yr * 365.25)) / 30.5);
dy := avg - (yr * 365.25) - (mh * 30.5);

CStr (yr, 0) + "y  " + CStr (mh, 0) + "m  " + CStr (dy, 0) + "d"


 Like I said before, I'm not really sure how to average a number of years, months and days.  What that formula does is get an average number of days and divide that by 365.25 to get the years, and divide the remainder by 30.5 to get the months, and what's left is the days.  If you'd rather use 365 days for a year or 30 days for a month, just change all of the references to 365.25 or 30.5 accordingly.


 If that gives you what you're looking for, you'd calculate the group 2 and 3 averages in the same way.

 Add running totals for the number of days and the count that are reset "on change of group" 2 and 3, and create new formulas for GF2 and GF3 that calculate the average from those running totals.

 Once you're finished, you can delete the formula from the detail section if you like (and shrink the section again).  I only had you put the formula there to make it easier to create the running totals.  Once they've been created, that formula doesn't need to be there anymore.  You can also delete the various running total fields that were added to the report if you like.  If you delete those fields from the report, the running totals will still be there for the average formulas to use.  They just won't be shown on the report.

 James
0
 

Author Closing Comment

by:ReportsupportAbility
ID: 31600316
Thanks James
0
 
LVL 34

Expert Comment

by:James0628
ID: 24924379
You're welcome.  Glad I could help.

 James
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now