Omit data based on criteria in reports

M

Michele E

I have a report which gives an employee's productivity rating, based on a set
standard. This standard depends on what product the employee is running at
the time. However, some times the employees run products that do not have
standards associated with them (trial products for instance).
A daily report tells:
Employee Product How much was produced How much was
expected (standard) % Expected (How much was produced/How much was
expected)
When a trial product is run on a given day, the % Expected number is Null
(=IIf([How much was expected] = 0, Null, [How much was produced]/[How much
was expected]). I have set all trial products standards to 0.
This works well.

However, now I have created a report that summarizes a particular operator
for a given time period. How do I get an "Average % Expected" for a certain
time frame to exclude the days when the trial product (which has 0 as the
amount expected)?
Currently it is adding everything and giving higher than accurate Avg %
Expected values. The trial product being produced will have a How much was
produced number 0 or greater, but the How much was expected number will
always be 0.

Thanks.
Michele E
 
K

KARL DEWEY

Try this --
=Sum(IIf([How much was expected] = 0, 0, [How much was produced]))
/Sum(IIf([How much was expected] = 0, 0, [How much was expected]))
 
M

Michele E

Thank you so much, Karl.
That worked!
--
Michele E


KARL DEWEY said:
Try this --
=Sum(IIf([How much was expected] = 0, 0, [How much was produced]))
/Sum(IIf([How much was expected] = 0, 0, [How much was expected]))

--
Build a little, test a little.


Michele E said:
I have a report which gives an employee's productivity rating, based on a set
standard. This standard depends on what product the employee is running at
the time. However, some times the employees run products that do not have
standards associated with them (trial products for instance).
A daily report tells:
Employee Product How much was produced How much was
expected (standard) % Expected (How much was produced/How much was
expected)
When a trial product is run on a given day, the % Expected number is Null
(=IIf([How much was expected] = 0, Null, [How much was produced]/[How much
was expected]). I have set all trial products standards to 0.
This works well.

However, now I have created a report that summarizes a particular operator
for a given time period. How do I get an "Average % Expected" for a certain
time frame to exclude the days when the trial product (which has 0 as the
amount expected)?
Currently it is adding everything and giving higher than accurate Avg %
Expected values. The trial product being produced will have a How much was
produced number 0 or greater, but the How much was expected number will
always be 0.

Thanks.
Michele E
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report that calculates based on another field's criteria 2
Autofilling information based on 2 other fileds 1
reporting even though a calculation made is dividing by zero 14
How 2 avoid sub-reports? 2
Generate reports directly to Email 3
Windows 10 Error: 'CreateProcessWithTokenW' was not declared in this scope 2
Creating a chart on a report 2
SQL Help - Criteria Based on 1 Table 2 Fields 2

Top