Formula to calculate Average based on previous 4 dates 2

[jbecker614]

Using Crystal Reports 11
Pervasive Database

I need to create a fomula for a client and I could use a little assistance.

This is based on a table that has a record of employee hours worked based on pay dates. I need to calculate the average hours worked based on the previous 4 pay dates from a date parameter.

i.e.

Sample records in the data:

Emp # Hours Pay Date:
1 10.5 10/21/2005
1 17 10/07/2005
1 20 09/22/2005
1 16 09/08/2005
1 18.4 08/25/2005

The table would have this information for each employee and every pay date in the system. I can group the report by employee and list the pay dates in decending order. The client wants to have a parameter to select a date and then calculate the average from the 4 previous dates.

So the average formula should calculate 10.5 +17+20+16 / 4.

Any suggestions on how to select the 4 previous dates from the parameter selected starting date?

 

[synapsevampire]

Hard to know how to deal with this as the dates aren't consistent, Thursday in Sept, Friday in October, so you need to define the rules for determining the paydays.

I guess that you might otherwise cheat it by grouping by employee and sorting the dates in descending order and adjusting the record selection and using the three formula method as in:

Record selection:
Report->Selection Formula->Record
(
{table.date} >= currentdate-30
and
{table.date} <= currentdate
)

Then use the three formulas:

Employee group header formula:
whileprintingrecords;
numbervar counter:=0;
numbervar Amount:=0

Details formula:
Employee group header:
whileprintingrecords;
numbervar counter:=counter+1;
numbervar Amount;
if counter < 5 then
Amount:=Amount+{table.amount}

Employee group footer formula:
whileprintingrecords;
numbervar counter;
numbervar Amount;
Amount/counter

The above also works if they have worked for less than 4 payperiods.

-k

 

[jbecker614]

synapsevampire:

Thanks! The counting works correctly. I need to create the average now. I am going to try a few tweaks to the formulas to get it to divide by the total number that is in the counter, unless you have any other suggestions.

Regards,

Jamey Becker

 

[jbecker614]

Sorry:

I noticed that you did include the average calculation. It doesn't seem correct yet. I am looking into it. The first employee had 80 hours each period. It correctly totaled to 320 hours but the average calculation in the footer only displays 7.80.

 

[jbecker614]

synapsevampire:


Ok, I found the issue. The counter does not reset. The formula in the group footer was dividing 320 total hours by 41 (which was the total # of pay dates in history).

I need to figure out a way to divide the 320 total by the number 4 or for the total number of pay dates if the employee has less than 4 pay periods.

 

[lbass]

Change the footer formula to:

//Employee group footer formula:
whileprintingrecords;
numbervar counter;
numbervar Amount;
if counter < 4 then
Amount/counter else
Amount/4

-LB

 

[jbecker614]

Lbass:

Perfect! I don't know why I didn't think of that. Must be tired.

Thanks to you and synapsevampire