Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Creating an Average formula using specific records 3

Status
Not open for further replies.

dbew

MIS
Sep 18, 2002
49
US
Hi everyone,

Can someone please assist me with creating averages in Crystal Reports 8.5? I've posted here a couple of times and I find it helps when I provide examples when dealing with the issues I present. So here it goes:

I have a very basic report containing an employee's Social Security number, Name, hire date, and hourly training wage. There are no groupings and no sorts. It's just a standard list of what's in the database's table. Basically, this is how it looks like after Crystal has finished reading the records:

SS#/Name/Hire Date/Hr Training Wage
111-11-1111/Katie Couric/01-01-2002/$30.00
222-22-2222/Matt Lauer/02-01-2002/$25.00
111-55-5555/Barbara Walters/04-01-2002/$0.00
999-33-3333/Al Roker/03-01-2002/$18.00
555-99-9999/Ann Curry/08-01-2002/$17.00

This is what I would like to do: I would like to calculate the average of the hourly training wages BUT, the calculation should only include wages greater than $0.00. ALSO, I want only those employees hired before a certain date.

So for example, I want to take an average of the hourly training wages for employees hired on or before 07-01-2002 with a wage greater than $0.00. From the sample data above, these are the records that should be included in the average calculation:

111-11-1111/Katie Couric/01-01-2002/$30.00
222-22-2222/Matt Lauer/02-01-2002/$25.00
999-33-3333/Al Roker/03-01-2002/$18.00

The average should be $24.33.

That's why I created a formula field that labeled each employee record as being "True" if they met this criteria and "False" if it didn't. Now:

SS#/Name/Hire Date/Hr Training Wage/Average
111-11-1111/Katie Couric/01-01-2002/$30.00/True
222-22-2222/Matt Lauer/02-01-2002/$25.00/True
111-55-5555/Barbara Walters/04-01-2002/$0.00/False
999-33-3333/Al Roker/03-01-2002/$18.00/True
555-99-9999/Ann Curry/08-01-2002/$17.00/False

I thought this would help make things easier and I could create an average formula that took the hourly training wages for only those records that had True in the "Average" column. But I haven't been successful.

Any assistance would be greatly appreciated!


 
Hi !

Insert two more formulafields in your detail section:

@RecordsIncluded
if yourformula = True then
1
else
0


and the other one

@Wage_included
if yourformula = True then
Hr Training Wage
else
0


Insert a Grand Total for both formulas.

Now can you create the formula that find the average and place it in your ReportFooter:

Your GrandTotal Wage_included / Your GrandTotal RecordsIncluded

I hope you understand my explanation

/Goran
 
Goran, I understood your explanation perfectly!!! And best of all, it worked perfectly!!! THANK YOU SO MUCH!!! Here what I did based on your instructions:

1. I created two formulas (@RecordsIncluded and @WagesIncluded) exactly like you said, which thus created two new columns in the Details section. All "True"s had a one (1) next to it as well as its wage rate.

2. I created Grand Totals (Sum of @RecordsIncluded and Sum of @WagesIncluded) for both columns. Sum of @RecordsIncluded yielded 3,798 and the Sum of @WagesIncluded yielded $20,160.23.

3. Finally I created a formula (@AVGTRN) like so:
Sum ({@WagesIncluded}) / Sum ({@RecordsIncluded})

And viola...$5.31!!!

I can't thank you enough! Can't I vote for you at Tek-Tips? :)
 
Your vote for goranm for TipMaster of the Week has been submitted.

Thank you for letting goranm know this post was helpful.


Thanks again, Goran!
 
Hi dbew,

I know you already have a solution, but here is another alternative that I stumbled upon after much struggle and which I found pleasing for its simplicity:

Create a formula "Meets Criteria":

if ({Hire_Date} <= Date(2002, 07, 01) and
{hourly_wage} > 0) then 1 else 0

Then, using the Running Total Editor, select {hourly_wage} as the field to be evaluated and choose &quot;Weighted Average&quot; as the summary function. When the &quot;with&quot; box appears, select the formula {@meets criteria}. In your case, select evaluate on change of record and reset never. The wage is multiplied by the result of {@meets criteria} and calculations (sum and count) only occur on those records that meet the criteria.

This method is especially handy if you have duplicated records due to left joins where you must use running totals on change of some field, e.g, SSN.

-LB
 
Hi LB!

Thanks for the alternative solution to my issue. I tried it out just now and it also returned $5.31 as my hourly training wage average. THANK YOU!!! And this one required less steps too!

Like you instructed, I first created a formula field that requsted Crystal to return a &quot;1&quot; if the criteria was met, otherwise 0. Then I accessed the Create Running Total Field wizard and named the running total &quot;Average Wage.&quot;

In the wizard's &quot;Summary&quot; section, for &quot;Field to summarize,&quot; I chose the hourly training wage field. For &quot;Type of summary,&quot; I chose &quot;weighted average.&quot; And when the &quot;with&quot; field appeared, I chose the formula field I created earlier.

Finally in the wizard's &quot;Evaluate&quot; section, I selected &quot;For each record.&quot; And in the &quot;Reset&quot; section, I selected &quot;Never.&quot;

THANK YOU!!!
 
Your vote for lbass for TipMaster of the Week has been submitted.

Thank you for letting lbass know this post was helpful.


Thanks again, LB!
 
Hi LB,

I wanted to calculate another average based on different criteria but I encountered a problem. I created &quot;Meets Criteria 2&quot; and saved it. Then when I went to the Create Running Total Field wizard, I couldn't select the &quot;Meets Criteria 2&quot; formula because it wasn't on list to the left (&quot;Available Tables and Fields:&quot;).

What's going on? Can a report only calculate one average? I did leave the first running total in the report because I want both averages to appear.

Please help!
 
Hi dbew,

Yes, you can do many averages in one report and many running totals. What was your formula? Some formulas don't appear as field selections because of the type of summary they entail. I'm not sure I can explain why, but I think it's because of the number of passes through the data required (as far as I know--I'm still learning!.

Maximums within groups are an example. For example, if you had a formula {@maximumhiredate} for the most recent hire date by employee ID (for employees hired multiple times) as follows: maximum({hire_date}, {ID}), where records are grouped on ID, then I don't think you can use {@maximumhiredate} for grouping (e.g., all employees grouped by most recent hire date) or for certain other calculations which would require a third pass through the data. The way I think about it (and I might be wrong!) is that the first pass populates the fields, the second pass is a scan of those fields to determine the instance that constitutes the maximum, and an attempted third pass, would be to then say now group on the maximum.

Sometimes though, when you can't get a formula like {@maximum} to work (it doesn't appear on the field list}, you can achieve the same result by writing out the components of the maximum formula itself, i.e., go back to the pre-formula expression, thus removing the &quot;second pass.&quot; Using the running total editor, while you couldn't use (@maximumhiredate < Date(2002, 07, 01) in the &quot;evaluate on&quot; formula section, I think you could type in: maximum({hire_date},{ID}) < Date (2002, 07, 01).

Using the weighted average function in the running total editor that I described earlier, I think you only have the option of selecting formulas from the field list, so that would be a limitation of this method.

So is it possible that your {@meets criteria 2} already involves a second pass?

-LB
 
Hi LB,

Let me expand on the example that I provided above:

SS#/Name/Hire Date/Hr Training Wage/Started Training
111-11-1111/Katie Couric/01-01-2002/$30.00
222-22-2222/Matt Lauer/02-01-2002/$25.00
333-44-7777/Dan Rather/04-01-2002/$30.00/04-25-2002
333-44-7777/Dan Rather/04-01-2002/$25.00/04-10-2002
111-55-5555/Barbara Walters/04-01-2002/$0.00
999-33-3333/Al Roker/03-01-2002/$18.00
555-99-9999/Ann Curry/08-01-2002/$17.00

My report will have &quot;duplicate&quot; records for certain employees. As you can see, Dan Rather has two records: one with a hire date of 04-01-2002, $30 wage, and a training start date of 04-25-2002; and another record with a hire date of 04-01-2002, $25 wage, and a training start date of 04-10-2002. Between the two records, the only differences are with the hourly training wages and the training start date.

For my second average calculation, this is what I would like to do: I would like to calculate the average of the hourly training wages BUT, the calculation should only include wages greater than $0.00. ALSO, I want only those employees hired before a 07-01-2002. AND if more than one record per employee, to only count the wage associated to the most recent training start date

So from the example, these records would be included in the average calculation:

SS#/Name/Hire Date/Hr Training Wage/Started Training
111-11-1111/Katie Couric/01-01-2002/$30.00
222-22-2222/Matt Lauer/02-01-2002/$25.00
333-44-7777/Dan Rather/04-01-2002/$30.00/04-25-2002
999-33-3333/Al Roker/03-01-2002/$18.00

The average would be $25.75.

How do I accomplish this using your method?

**********************************************************************************

To answer your question, the formula &quot;Meets Criteria 2&quot; I used was:

If {HIRE-DATE} < Date (2002, 07, 01)
and
NOT (Not onfirstrecord and {EMPLOYEE} = previous {EMPLOYEE})
and
{WAGE} > 0
then 1
else 0


Since the most recent training start date appears first for each employee, I figured adding &quot;NOT (Not onfirstrecord and {EMPLOYEE} = previous {EMPLOYEE})&quot; would evaluate only the first record of the duplicates. But like I said, the &quot;Meets Criteria 2&quot; formula never appeared on the list.

I realized this morning that when I removed that part of the code (&quot;NOT (Not onfirstrecord and {EMPLOYEE} = previous {EMPLOYEE}), then it appeared. But of course, it preceeded to include duplicate records for the average calculation which is not what I want.



 
Hi dbew,

The way I would do this is:
1- Revert to the {@meets criteria} formula for the weight in the weighted average, i.e., eliminate the &quot;Not(Notonfirstrecord and {employee} = previous{employee})&quot; portion of your formula.
2- Group on SSN and hide the group, if you wish.
3- Sort on training date - descending
4- In the running total editor: select hourly wage, weighted average, and @meets criteria
5- Select &quot;Evaluate on change of group&quot;, selecting Grp#_: SSN.
6 - Reset Never.

By sorting in descending order by training date, the most recent wage will be at the top of the details field. Then when the running total evaluates on change of SSN, only the topmost field will be included in the calculation.

I hope this does the trick...

-LB
 
LB, YOU ARE A GENIUS!!!!!!!!!!

I have over 2,000+ valid employee records in my report so it was difficult to tell if it worked in the beginning. But I decided to use a sample of employees (I created a select formula to include only 3-4 Social Security numbers). And from there I compared the weighted average value with my calculator...IT WORKS!!!

Thank you, thank you, thank you!!! :)

BTW, what field are you in? For someone who's &quot;still learning,&quot; you're very good!
 
The second time this week you've helped me solve a problem. You deserve it!!!

Your vote for lbass for TipMaster of the Week has been submitted.

Thank you for letting lbass know this post was helpful.
 
Hi dbew,

Workforce development. Glad that I could help, and thanks for your nice (and flattering) remarks!

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top