Hello,
I am trying to calculate rolled throughput yield(RTY) using operational first pass yield(FPY) data for each work order and then calculate an average of the results per selected time period. I also need to report these values based on Time + Product. (TLDR at the end)
I found this article and thanks to @sunny_talwar 's solution using "Exp(Sum(Aggr(Log([output]/(([output] + [scrap] + [rework] + [byprod]))), [workorder], [operation])))" i can calculate the FPY for each workorder, but there is one problem with the expression. If the output is 0 then the result is 1 because Log function only works if output > 0. I even tried adding an IF in the expression like this : "Exp(Sum(Aggr(IF([output] = 0, 0, Log([output]/([output] + [scrap] + [rework] + [byprod]))), [workorder], [operation])))" but it did not change the result.
A small sample of the raw data i am working with is like this;
| Product | workorder | operation | keyflddate | output | rework | scrap | byprod |
| A | 20050363 | 30 | 2020-08-04 00:00:00.000 | 1 | 0 | 0 | 0 |
| B | 20100275 | 40 | 2020-08-04 00:00:00.000 | 4 | 0 | 0 | 0 |
| B | 20100275 | 43 | 2020-08-04 00:00:00.000 | 3 | 0 | 0 | 1 |
| B | 20100275 | 151 | 2020-08-04 00:00:00.000 | 2 | 0 | 0 | 0 |
| B | 20100275 | 151 | 2020-08-04 00:00:00.000 | 1 | 0 | 0 | 1 |
| C | 20080575 | 10 | 2020-08-04 00:00:00.000 | 1 | 0 | 0 | 0 |
| C | 20080575 | 30 | 2020-08-04 00:00:00.000 | 1 | 0 | 0 | 0 |
| C | 20080689 | 10 | 2020-08-04 00:00:00.000 | 1 | 0 | 0 | 0 |
| C | 20080690 | 10 | 2020-08-04 00:00:00.000 | 1 | 0 | 0 | 0 |
| C | 20080690 | 10 | 2020-08-04 00:00:00.000 | 1 | 0 | 0 | 0 |
| C | 20080690 | 10 | 2020-08-04 00:00:00.000 | 0 | 0 | 0 | 1 |
| D | 20060872 | 20 | 2020-08-04 00:00:00.000 | 9 | 0 | 0 | 0 |
| D | 20060872 | 60 | 2020-08-04 00:00:00.000 | 9 | 0 | 0 | 0 |
| D | 20060872 | 65 | 2020-08-04 00:00:00.000 | 8 | 0 | 0 | 1 |
| D | 20060872 | 80 | 2020-08-04 00:00:00.000 | 7 | 0 | 0 | 1 |
| D | 20060872 | 120 | 2020-08-04 00:00:00.000 | 6 | 0 | 0 | 0 |
| D | 20060872 | 133 | 2020-08-04 00:00:00.000 | 3 | 0 | 0 | 3 |
| D | 20060872 | 135 | 2020-08-04 00:00:00.000 | 0 | 0 | 0 | 3 |
| D | 20080599 | 10 | 2020-08-04 00:00:00.000 | 2 | 0 | 0 | 0 |
FPY for each work order should be like;
| material | workorder | RTY |
| A | 20050363 | 100.00% |
| B | 20100275 | 56.25% |
| C | 20080575 | 100.00% |
| | 20080689 | 100.00% |
| | 20080690 | 66.66% |
| | 20080692 | 100.00% |
| D | 20080599 | 100.00% |
| | 20060872 | 0.00% |
Now comes the part i am totally clueless about how to do. I am trying to visualize the data with a line chart that uses RTY average of workorders as a measure and time as dimension and another chart to show top 5 lowest FPY results for each product and operation combination per selected time period that users average of operation FPY weighted with operation input(output+rework+scrap+byprod) as a measure and product+operation combination and time as 2 dimensions. Both charts have Year, MonthName and Week Year as alternative dimensions so the report can be changed according to required time period.
TLDR;
I am trying to calculate RTY using "Exp(Sum(Aggr(Log([output]/(([output] + [scrap] + [rework] + [byprod]))), [workorder], [operation])))" but the result is 1 when output is 0, it should be 0.
I also need to calculate RTY for work order only and use that data to get average results for Time dimension.
Any input about the formulas or how to actually get the results i am trying would be appreciated.
