I have data that is in this form:
|LH||02 - Meats Processing PI||48 PI MIDWEST 2||1338||2||1230||13-Mar-13||MAR-13|
|LH||02 - Meats Processing PI||48 PI EDD||1981||2||1230||12-Mar-13||MAR-13|
|LH||25 PI EDD||2186||2||1230||12-Mar-13||MAR-13|
|OP||99 - Others||OP OUTSIDE||1603.18512||1||1226||14-Feb-13||FEB-13|
|PH||06 - Inshell AL||PHS INSHELL BAG||27193||-1||1222||18-Jan-13||JAN-13|
|PH||06 - Inshell AL||PHS INSHELL BAG||43000||1||1222||18-Jan-13||JAN-13|
|PH||06 - Inshell AL||PHS INSHELL BAG||61759||-1||1222||21-Jan-13||JAN-13|
|PH||06 - Inshell AL||PHS INSHELL BAG||46594||-1||1223||21-Jan-13||JAN-13|
I want to calculate yield for line_name and line group over days/weeks/months/etc. Yield is calculated by dividing LBS PROD with line type 1 by LBS PROD with line type -1. I was able to accomplish this with a table calculation, however I want to do more calculations from this yield percentage.
For instance different machines (lines) have different acceptable yields so a huller might be ok running with a 75% yield, however a roaster must be above 89%. So I wanted to run a case statement so that I could color the bar charts by acceptable or not acceptable yields depending on their line group, however I have no idea how to make the calculation in the case statement
CASE [LINE GROUP]
WHEN "01-Roasters" THEN IIF(??????>89%, "Acceptable", "Not Acceptable")
How should I go about doing this?