Problem with calculating OEE via custom expression in Spotfire

[Martin Sokolov]

Hi,

I have one question regarding custom expressions in Spotfire. I'm trying to calculate OEE (Overall Equipment Effectiveness) via custom expression in cross table.

The problem that I'm having is that when there is more than one work order in one shift, I'm getting incorrect (random) value for OEE. I'm only getting the correct value when there is only one work order.

My question is, how to sum the OEE values when there are more then one work order in one shift via custom expression on the axis of the cross table?

Here is the table from which I'm trying to calculate OEE:

 

I've tried creating Calculated column with the OEE formula and I'm always getting fixed value for all work orders, for example in my case 0.39, that's why I'm using custom expression on the cell value axis on the cross table.

Thanks.

[Gaia Paolini]

Can you provide the formula you are using for OEE, the original table and the settings of your cross table?

[Martin Sokolov]

Sum([PlannedQTY]), Sum([QTY]), Sum([RunTime]) / Sum([PlannedProductionTime]) * 20 / (Sum([RunTime]) * 60) * Sum([QTY]) / Sum([QTY]) * 100 as OEE

Original table:

Cross table:

[Gaia Paolini]

Your formula simplifies to

1 / Sum([PlannedProductionTime]) * 20 / 60 * 100

I think it is an issue with putting parentheses to guarantee the order of calculation.

[Martin Sokolov]

Sum([PlannedQTY]), Sum([QTY]), Sum([RunTime]) / Sum([PlannedProductionTime]) * 20 / (Sum([RunTime]) * 60) * Sum([QTY]) / Sum([QTY]) * 100 as OEE

The result in the cross table for OEE is the same when I have code like above (without parentheses) and below (with parentheses):

[Gaia Paolini]

Your formula is (excluding the two other axes Sum([PlannedQTY]) and Sum([QTY]):

Sum([RunTime]) / Sum([PlannedProductionTime]) * 20 / (Sum([RunTime]) * 60) * Sum([QTY]) / Sum([QTY]) * 100

which if we substitute Sum([RunTime])=A and Sum([QTY])=B for clarity is:

A / Sum([PlannedProductionTime]) * 20 / (A * 60) * B / B * 100

which still simplifies to (removing A/A and B/B):

1 / Sum([PlannedProductionTime]) * 20 / (60) * 100

[Gaia Paolini]

Without claiming to know anything about OEE, I thought that maybe you could include the planned quantity vs the (quantity - rejected quantity) to account for the production component. I don't know about the time component, perhaps dividing by run time again can be removed?