If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Omit data based on criteria in reports
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 |
#2
|
|||
|
|||
Omit data based on criteria in reports
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" wrote: 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 |
#3
|
|||
|
|||
Omit data based on criteria in reports
Thank you so much, Karl.
That worked! -- Michele E "KARL DEWEY" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|