Can't open your workbook, but I can open the excel sheet.
Your example has 60 rows. Is that the actual table? Or will there actually be more rows? And will the table change? Or will it be static?
If static and limited, I would create a calc rather than a table.
IF [DPMO] <= 3.4 then .999997 elseif
[DPMO] <= 5 then .999995 elseif
[DPMO] <= 900000 then .1 elseif
[DPMO] <= 902000 then .08 else 0 END
(And a parallel calc to load up [Sigma].)
Many thanks for the response.Please find the packaged workbook developed in 9.1
The solution provided is really helpful but excel abridged table(xls) might grow depending on my DPO.
So what the solution we decided on it to calculate the Yield based on the DPO and DPMO fields.
Quality_Calculation.twbx 38.9 KB
I thought I could make this happen, but the ideas I tried didn't do it.
The problem is that you have to blend the sigma table with the data set. I added a column to the sigma table with a value of 1 for all rows. And added a calc to the data table with a value of 1. That way all sigma rows would get connected to all data set rows. Then what I had hoped to do was find the row in the sigma table that had the minimum value that was still larger than the DPMO value from the data set.
The idea failed because you have to pass aggregate values between data sources, and therefore you can do a MIN of a value that is already aggregated.
The next attempt was to approach this with the sigma table as the primary data source, but in the end I ran into the same limitations.
If I were faced with this issue I would ETL the data source data before hand to match up DPMO with Sigma levels.