Sum QTY if previous operation finished

[rohi__]

Hello Guys,

I'm trying to create a KPI that shows me the current status of orders to be completed.
For example, I want to evaluate the work center Q.
But now the step Prod must be completed first.
I have developed this formula so far, unfortunately, it does not work as soon as I have more than one entry.

aggr(if(len(Status)=0,sum(distinct {<Workplace= {'Q'}>} QTY)), Order, Workplace, Operation)

Can anyone help me?

Thanks in advance!

Sample Data:

Order	Operation	QTY	Status	Workcenter
1	10	70	Done	Prod
1	20	70	-	Q
2	10	71	Done	Prod
2	20	71	-	Q

The solution would be 141 (70+71)

Order	Operation	QTY	Status	Workcenter
1	10	70	-	Prod
1	20	70	-	Q
2	10	71	Done	Prod
2	20	71	-	Q

The solution would be 71

[agigliotti]

maybe this:

=Sum( aggr( if( count( {< Workplace = {'Prod'}, Status = {'Done'] >} Operation ) > 0, sum( {< Workplace = {'Q'} >} if( len(Status) = 0, QTY ) ) ), Order ) )

I hope it can helps.

[rohi__]

Hi @agigliotti ,

thanks so much for your fast reply!

It worked as expected!

The next task would be to make this formula dynamic. 

For example, I wanna calculate the KPI for a random work center, the one which I would choose with a filter.

How do I have to change that formula, that it would always check if the previous operation is finished and if yes it would calculate?

Thanks in advance!

[agigliotti]

I'm not sure if I well understood what you asked.

anyway let's try removing {< Workplace = {'Q'} >}

in order to get the value from the filter.