Print Page | Close Window

Calculating Average students per year

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=6437
Printed Date: 04 May 2024 at 2:22pm


Topic: Calculating Average students per year
Posted By: shefe
Subject: Calculating Average students per year
Date Posted: 22 May 2009 at 5:07am
Hello All,

Can someone please help me, i am trying to determine the average time a student have been in our school per fiscal year but based on current semester. Here is a sample.

we are currently in FY2009 and let say we have 5 students enrolled, i want to know the average yrs those students have been here in FY2009 which should be 14 divided by 5 then the average should be 2.5.. Both fields is String and i am using Crystal Report 8.5

Student Name is in Detail a and FYacademicYR is in Detail b
Bobbi Booth
FY2009
FY2008
FY2007
FY2006
FY2005

Savio Zigbi
FY2009

Muhammad Bittu
FY2006

Lucia Zubia
FY2009
FY2008

Shuzyman Kobii
FY2009
FY2007
FY2006

Abdul Jabbar
FY2009
FY2005
FY2004


Replies:
Posted By: lockwelle
Date Posted: 22 May 2009 at 6:39am
Create a formula that calculated the average.
Average(Count({table.fiscalyrField}), {table.studentNameField})
 
place the formula in the footer of the grouping for the student name field (could be in the header if desired).
 

Posted By: shefe
Date Posted: 22 May 2009 at 7:55am
i got an error "Summary/Running total could not be created

Posted By: DBlank
Date Posted: 22 May 2009 at 8:18am
change your set up from using details a and details b to grouping on student and removing details "a".

Posted By: shefe
Date Posted: 22 May 2009 at 8:44am
I did the same thing and still getting that error though the only grouping this time around is just student names with Student name and @FY Academic Year IN DETAIL AREA and the group header has the student name while the group footer has the DistinctCount of @FY Academic Year.  The @FY Academic Year per student shows up accurately. and when i create a formula called average counter by putting in  

Average(Count({@FY Academic Year}), {Student.Student Name})  ,,, 

i get the  error "Summary/Running total could not be created"

Posted By: DBlank
Date Posted: 22 May 2009 at 11:38am

actually I don't think you can use Average function on another summarized field.

can you take the distinctcount (@FY) / distinctCount(student)  ?

Posted By: shefe
Date Posted: 22 May 2009 at 12:13pm
I am getting a zero, strange i don't know why 

Posted By: DBlank
Date Posted: 22 May 2009 at 12:35pm

Do some testing but my guess is the distinctcount of the @FY is the culprit. It is probably only about 5 based on your sample data. My guess is you may need another formula to get the correct distinct count of FY. You don't really want a count, you want a distinct count per student, correct? Therefore you cannot just use count or distinct count on that field. YOu will need to add the student (or student ID if there is one so you do not run into same name problems) and the FY together and do a distinct count on this formula field.

Something like :
{table.FY_field} + " - " + totext({table.student_id_field}),0,"")
 
From there you can divide it by the distinctcount of the student (preferable ID#).
Set up two seperate fields to get your counts so you can test them. From there you can tweak them independently and then use them together for the average (divide by).
 
Also if you do end up grouping on the stsudent name I almost always use a formula field for this instead of the actual name (I have an ID # associated to names in my primary DB). THis way you never accidently group two different people into one place. Something like:
{table.Namefield} + " (" + totext({table.idfield}),0,"") + ")"
If you want to hide the ID field still use this to group, suppress the group name and drop the name field into the header. It still keeps them seperate but hides the ID.

Posted By: shefe
Date Posted: 23 May 2009 at 10:24pm
I tried 2 formulas and they actually give the right answer but now i need to sum it for per student, i think i need a formula that stores the value...

the first formula is IF {@FY Academic Year}="FY2009" THEN distinctCount ({@FY Academic Year},{Student.Student Name} ) and second formula is distinctCount ({@FY Academic Year},{Student.Student Name} ) and the formulas gives me the same answer,  i just the to add the sum of the value if @FY Academic Year="FY2009"  FOR EACH students

Posted By: DBlank
Date Posted: 24 May 2009 at 7:58am
You cannot sum on either of these options That is why I recommended another formula to take your @FY + student Id. From there you can just do a distinct count of that. It would be the same as summing your other  formulas at the group level.
You do no have to display it in the report to use it for a summary.

Posted By: shefe
Date Posted: 25 May 2009 at 5:47pm
I dont have student ID though so how do go about it

Posted By: DBlank
Date Posted: 25 May 2009 at 6:08pm
just use the name then. You may have issues if students have the same name but that would be problematic anyway. Create the formula adding the year formula to the name and do a distinctcount of this new formula.


Print Page | Close Window