Discussion board where members can get started with Qlik Sense.
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.
Hello, I changed the formula like below to workaround the problem.
if(Min(Aggr(Sum([Sheet1.output]), [Sheet1.Product], [Sheet1.workorder], [Sheet1.operation])) = 0, 0, Exp(Sum(Aggr(Log(Sum([Sheet1.output]) /Sum([Sheet1.output] + [Sheet1.scrap] + [Sheet1.rework] + [Sheet1.byprod])), [Sheet1.Product], [Sheet1.workorder], [Sheet1.operation]))))
In short, i have added an IF function to see if any operation output is 0 because if so the RTY result is 0 anyway.
To fix the first part, try this
Exp(Sum(Aggr(Log(Sum([output])/Sum([output] + [scrap] + [rework] + [byprod])), Product, [workorder], [operation])))
What is the expected output for the line chart based on the given sample data?
Hello and thank you for the quick reply but unfortunately adding the SUM did not work. I am still getting 1 as result when output = 0.
The time based result for sample data would be like below, its the average of RTY values for the selected period.
keyflddate | RTY |
2020-08-04 00:00:00.000 | 77,86% |
So, you mentioned that you wanted to get this, right?
And when I use the expression I sent you in QlikView (Qlik Sense should work the same way), I get this
The number from the image matches what I get... what am I missing? Can you point out what numbers need to change?
The result i am getting is like this;
Sheet1.Product | Sheet1.workorder | RTY(Sample) |
A | 20050363 | 100.00% |
B | 20100275 | 56.25% |
C | 20080575 | 100.00% |
C | 20080689 | 100.00% |
C | 20080690 | 66.67% |
D | 20060872 | 38.89% |
D | 20080599 | 100.00% |
huh, can you share a sample qvf where I we can see this?
I am sharing the qvf file. I used an excel file with the sample data as source
Also we are using February 2020 Patch 3 - 13.62.11 version.
Any chance with whats wrong with my file? 🙂
Also is it possible to calculate RTY value at LOAD statement? It would help me use that as a field for other calculations.
Yes, I am not sure what is causing this, because in QlikView it is not showing me 38.89%, but in Qlik Sense it is.
Hello, I changed the formula like below to workaround the problem.
if(Min(Aggr(Sum([Sheet1.output]), [Sheet1.Product], [Sheet1.workorder], [Sheet1.operation])) = 0, 0, Exp(Sum(Aggr(Log(Sum([Sheet1.output]) /Sum([Sheet1.output] + [Sheet1.scrap] + [Sheet1.rework] + [Sheet1.byprod])), [Sheet1.Product], [Sheet1.workorder], [Sheet1.operation]))))
In short, i have added an IF function to see if any operation output is 0 because if so the RTY result is 0 anyway.