AGGR or something else?

[itatspectrum]

Not sure if I'll be able to get what I need with preparing the data more in the load section. 

I have the following data set. It represents production bookings on operations per job that have been done. 

I'm looking to identify current Work Centre , the answer is 90 INSP B. We ignore missing bookings which are assumed based on a later op having been completed. ie 60 is a missing booking, having a op that is late already booked as completed.

I'm also looking to count the number of missing bookings, the answers is 1, line 60. 

I'm also looking to get the percentage completed, assuming that the missing line 60 is also assumed as completed. the answer is 53.3% (8 completed / 15 total ops)

Bearing in mind I have a dataset that has lots of orders with lots of lines and this is just an example of one order. 

Should I be loading summaries during the load at the order header level, or can I use something in the charts to do this?

Cheers

Steve

WO OpKey	OP MOS Type	OP Num	OP WC ID	OP WC Status	Qty
0135017-001	S	10	KIT B	Complete	8
0135017-001	S	20	BNCH B	Complete	8
0135017-001	S	30	WELD B	Complete	8
0135017-001	S	40	NDT B	Complete	8
0135017-001	S	50	PTMK B	Complete	8
0135017-001	S	60	INSP B	Open	8
0135017-001	S	70	TREAT	Complete	8
0135017-001	S	80	TREAT	Complete	8
0135017-001	S	90	INSP B	Open	8
0135017-001	S	100	SCON10	Open	8
0135017-001	S	110	INSP B	Open	8
0135017-001	S	120	PTMK B	Open	8
0135017-001	S	130	INSP B	Open	8
0135017-001	S	140	PTMK B	Open	8
0135017-001	S	150	INSP4 B	Open	8

[robert99]

Hi

Maybe I'm wrong but don't see why you need to use AGGR

 Does the OP NUM always increase in values of 10?

Because what you (might) need to do is join the row above and below to every row. I have done this once by just loading the table two more time but joining to the OP NUM but once using plus 10 (NEXT) and once using minus 10 (PREV)

Another way is to use peek and order by Descending and accending

Then count missed booking > COUNT ({<OP WC Status = {Open} ,PREV = {Complete} , NEXT = {Complete} >} OP Num)

etc

% complete. Use something like

COUNT ({<OP WC Status = {Complete} >} OP Num)

/

COUNT ({<OP WC Status = {Complete, Open} >} OP Num)