Help with Formula

Gordon Hughes
Gordon Hughes used Ask the Experts™
on
Hi
I have a report with hours shown see attached
What I would like is a formula which where the WO Group is "Grouped" to divide the hour figure by the number in that group which may vary from 2 line to many, the criteria for the group is the task number and the next due date
Where the WO Group is "Separate" the hours figure remains as is

Gordon
Sample-report-data.docx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You want to divide Hours by the number of records in the group (ie. calculate the average)?

 Is the Hours column a field (ie. that same figure is repeated in each record), or a group total, or something else?

 If Hours is a group total, you can right-click on the Hours field on the report (the field from your data, not the group total), select Insert > Summary, change the summary type to Average, and change the "location" to be for the group.

 Or, you can calculate the group average in a formula using

Average ({Hours field}, {group field})
  --or--
Sum ({Hours field}, {group field}) / Count ({Hours field}, {group field})


 If the Hours column is a field from a single record, you can use a formula like the following:

{Hours field} / Count ({Hours field}, {group field})


 If the Hours field could ever be null, you may need to use the CR report option to convert null values to default values (otherwise, the group count or total may not be correct).

 James
Gordon HughesDirector

Author

Commented:
Hi James
Good to hear from you.
The issue is not the summaries.
There are two types of the hours calculation, will try to explain
On each record as per my attachment, it is either a sperate job, defined as seperate in the wo group, the hour figure for this is correct. Where the wo group field says group, there are several jobs with the same task number and date due, the report duplicates the hours for each task, this is wrong, what i need is to be able to divide the hours shown by the number of jobs with the same task number and same date, so if the hour shown was 10 hours and there were 10 jobs it wants to show 1 hour per job
Hope this makes sense.
Gordon
Are CLOCK-HOUR-6M and 18/03/2019 the task number and date that you're talking about?  I'm guessing that those are the fields that you're talking about, but you said in the document that you posted that there were 15 lines in that "group", and I see 16 lines with that task number and date.  Also, your document shows two lines for COMPACTER-12M and 20/03/2019, but it says that they are "Separate".  If those are the fields that you're talking about, it seems like those two lines should say "Grouped".

 It might help if you could post the report (with saved data, if possible).  Right now I'm just making a lot of guesses about what your data actually looks like, and how your report is structured.  For example, are the lines in the document that you posted detail lines, or group totals?  Are there other groups or sorts in the report?  Task Number appears to be the first column on the report, but the report doesn't seem to be sorted by Task Number.  And so on.

 James
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Gordon HughesDirector

Author

Commented:
Hi James

Report attached

Gordon
Work-Orders-Projections-showing-Craf.rpt
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Check the report I modified.  I added a group on the tasknumber then used the count of the tasknumber to calculate the corrected hours for grouped tasks

If you need to calculate a new summary you will need to do a manual calculation or use a running total
Work-Orders-Projections-showing-Craf.rpt
mlmcc got to this before I did, so I'll wait until you've had a chance to check his report.  If that doesn't have what you're looking for, post back and I'll take a look at your report.

 FWIW, I will say this ...

 mlmcc said that he added a group on task number, and it sounded to me like your "group" was on both the task number and date.  But, again, I'm just guessing.

 James
Gordon HughesDirector

Author

Commented:
Hi Guys
mimcc has got the individual hours working spot on.
The only issue now is how to do this that mimcc refers to
"If you need to calculate a new summary you will need to do a manual calculation or use a running total"

Like to old version I need a total shown at Group Header #1and a grand total in the report footer

Gordon
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
YOu need the total to reflect the total of the new calculated field rather than the total of the original field, right?

If so it is done with 3 formulas
For summary at the group 1 footer the basic idea is

Add to the group 1 header
Name - InitTotals
WhilePrintingRecords;
Global NumberVar TotalHours;
TotalHours := 0;
''

Open in new window


In the detail section

Name - CalcTotals
WhilePrintingRecords;
Global NumberVar TotalHours;
TotalHours := TotalHours + {@CorrectedHours};
''

Open in new window


IN the Group 1 footer
Name - DispTotals
WhilePrintingRecords;
Global NumberVar TotalHours;
TotalHours 

Open in new window


If you need the grand total then you need another variable declared in the report header
The calculation formula changes to also calculate the grand total
A display formula is needed in the report footer

mlmcc
Gordon HughesDirector

Author

Commented:
Mimcc
Will add these and yes i need a grand total in the report footer
Gordon
To add to what mlmcc said ...

 If you need a total for the zCorrectedHours formula, the problem is that that formula uses a summary function (Count), and CR won't do a summary on a summary, so you can't just have CR give you a total for that formula.  You have to calculate the total manually, using a variable (as in the formulas that mlmcc posted), or you might be able to use a running total.

 Either way (formulas and variable, or running total), you won't be able to show that total in the group header, because the total is calculated as the records are read/processed, so you won't have a total until you get to the last record in the group.  That total will have to be in the group footer.

 If you really need to see that total in the group header, one option would be to use a subreport.  You could put a subreport in the group header that would read the records for the current WOTYPE (and the appropriate dates, etc.) and calculate the total.  The subreport would just output the total at the end, and suppress everything else.

 The basic downside to that approach is that your report ends up reading everything twice (once in the main report and again in the subreport).  If the report is not reading that much data, then that may not be a problem.  But if it's reading a lot of data, that's a lot of extra work.

 James
Gordon HughesDirector

Author

Commented:
mimcc/James

I have added the formula as per mimcc, see attached

The section totals look great,
Any other suggestions to get the grand total, am sure you guys can sort it for me!!
Gordon
Task-Projections-showing-Craf.rpt
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
I added the additional formulas and made a change to be more efficient and less prone to errors

mlmcc
Task-Projections-showing-Craf-rev2.rpt
Gordon HughesDirector

Author

Commented:
Excellent support as normal
Great stuff you guys

Gordon

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial