Solved: Re: How to Calculate RTY using FPY and Visualize t... - Qlik Community

VolkanKu · ‎2020-08-11

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.

VolkanKu · ‎2020-08-19

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.

View solution in original post

sunny_talwar · ‎2020-08-11

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?

VolkanKu · ‎2020-08-11

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%

sunny_talwar · ‎2020-08-11

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?

VolkanKu · ‎2020-08-12

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%

sunny_talwar · ‎2020-08-12

huh, can you share a sample qvf where I we can see this?

VolkanKu · ‎2020-08-12

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.

VolkanKu · ‎2020-08-17

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.

sunny_talwar · ‎2020-08-17

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.

VolkanKu · ‎2020-08-19

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.

How to Calculate RTY using FPY and Visualize the Data?