I think I have what seems to be a fairly tough problem. I'm having trouble calculate the number of parts that go through multiple machines/processes. Below is the data I receive to put into QlikView (headers with the red text). Each record has a quantity associated with it but I cannot just simply take a sum of the Quantity field to get what was produced because it will be overstated. I tried using set analysis and the AGGR expression but the issue comes from parts that cross over into different months (like part 2222 below). In my tables below, part 2222 should have a total Routing_Time of 1.25 in May and 1.00 in June with 0 completed parts in May and 4 completed parts in June.
I think the solution is to create a field in the script that only has the Completed Quantity for the records that are the final step in the process (see Desired_Result field in the second table). Unfortunately, the only way to identify that a part is truly completed is if it stops showing up in the data.
I had a somewhat similar issue a month ago where a QlikCommunity user helped me using RecNo() and Previous() but I still can't figure this out.
Any help would be appreciated!!
JOB | PART | PROCESS | DATE | QUANTITY | ROUTING_TIME | Actual_Result |
ABC | 1111 | Machine_1 | 05/15/2014 | 10 | 0.50 | 10 |
ABC | 1111 | Machine_2 | 05/16/2014 | 10 | 0.75 | 10 |
ABC | 2222 | Machine_1 | 05/31/2014 | 4 | 0.50 | 4 |
ABC | 2222 | Machine_2 | 05/31/2014 | 4 | 0.75 | 4 |
ABC | 2222 | Machine_3 | 06/01/2014 | 4 | 1.00 | 4 |
DEF | 1111 | Machine_4 | 05/31/2014 | 6 | 0.50 | 6 |
DEF | 1111 | Machine_5 | 06/01/2014 | 5 | 0.25 | 5 |
JOB | PART | PROCESS | DATE | QUANTITY | ROUTING_TIME | Desired_Result |
ABC | 1111 | Machine_1 | 05/31/2014 | 10 | 0.50 | 0 |
ABC | 1111 | Machine_2 | 05/31/2014 | 10 | 0.75 | 10 |
ABC | 2222 | Machine_1 | 05/31/2014 | 4 | 0.50 | 0 |
ABC | 2222 | Machine_2 | 05/31/2014 | 4 | 0.75 | 0 |
ABC | 2222 | Machine_3 | 06/01/2014 | 4 | 1.00 | 4 |
DEF | 1111 | Machine_4 | 05/31/2014 | 6 | 0.50 | 0 |
DEF | 1111 | Machine_5 | 06/01/2014 | 5 | 0.25 | 5 |