Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

How to Calculate RTY using FPY and Visualize the Data?

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;

Productworkorderoperationkeyflddateoutputreworkscrapbyprod
A20050363302020-08-04 00:00:00.0001000
B20100275402020-08-04 00:00:00.0004000
B20100275432020-08-04 00:00:00.0003001
B201002751512020-08-04 00:00:00.0002000
B201002751512020-08-04 00:00:00.0001001
C20080575102020-08-04 00:00:00.0001000
C20080575302020-08-04 00:00:00.0001000
C20080689102020-08-04 00:00:00.0001000
C20080690102020-08-04 00:00:00.0001000
C20080690102020-08-04 00:00:00.0001000
C20080690102020-08-04 00:00:00.0000001
D20060872202020-08-04 00:00:00.0009000
D20060872602020-08-04 00:00:00.0009000
D20060872652020-08-04 00:00:00.0008001
D20060872802020-08-04 00:00:00.0007001
D200608721202020-08-04 00:00:00.0006000
D200608721332020-08-04 00:00:00.0003003
D200608721352020-08-04 00:00:00.0000003
D20080599102020-08-04 00:00:00.0002000

 

FPY for each work order should be like;

materialworkorderRTY
A20050363100.00%
B2010027556.25%
C20080575100.00%
 20080689100.00%
 2008069066.66%
 20080692100.00%
D20080599100.00%
 200608720.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.

 

 

 

1 Solution

Accepted Solutions
Highlighted
Contributor II
Contributor II

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

10 Replies
Highlighted

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?

Highlighted
Contributor II
Contributor II

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.

keyflddateRTY
2020-08-04 00:00:00.00077,86%

 

Highlighted

So, you mentioned that you wanted to get this, right?

image.png

And when I use the expression I sent you in QlikView (Qlik Sense should work the same way), I get this

image.png

The number from the image matches what I get... what am I missing? Can you point out what numbers need to change?

Highlighted
Contributor II
Contributor II

The result i am getting is like this;

Sheet1.ProductSheet1.workorderRTY(Sample)
A20050363100.00%
B2010027556.25%
C20080575100.00%
C20080689100.00%
C2008069066.67%
D2006087238.89%
D20080599100.00%
Highlighted

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

Highlighted
Contributor II
Contributor II

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.

Highlighted
Contributor II
Contributor II

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.

Highlighted

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.

Highlighted
Contributor II
Contributor II

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