Sum if question

[dominicanlauf]

Hello everybody,

I have a table that deals with the number of produced pieces  and the reasons for error.

Another point is the consideration of samples. The number of samples depends on the number of produced pieces.

Allocation:

produced (>150, >280, >500,>1200) -> samples(8,13,20,32,50)

I tried to determine the result by an If -function.

If I use the formula for each order number, it works.

If(MPS_productionorder.amount > 1200, 50, if(MPS_productionorder.amount > 500, 32, if(MPS_productionorder.amount > 280, 20, if(MPS_productionorder.amount > 150, 13, 8))))

But once I calculate the sum (by year/month) it doesn't work.

sum(If(MPS_productionorder.amount > 1200, 50, if(MPS_productionorder.amount > 500, 32, if(MPS_productionorder.amount > 280, 20, if(MPS_productionorder.amount > 150, 150, 8)))))

Does anyone have an idea how I could solve the Problem?

[dominicanlauf]

P.S.: Doesn't work means the sum of samples is incorrect.

[marcus_sommer · Accepted solution]

I think you need to use an aggr() to enforce a calculation on the order number, for example with something like this (shortened the long fieldname):

sum(aggr(
pick(match(-1, amount > 1200, amount > 500, amount > 280, amount > 150, amount <= 150), 50, 32, 20, 13, 8),
Auftragsnummer))

- Marcus