topic Re: Sum if question in QlikView

Sum if question

dominicanlauf — Sat, 16 Nov 2024 20:44:32 GMT

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?

Re: Sum if question

dominicanlauf — Mon, 03 Jun 2019 07:38:52 GMT

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

Re: Sum if question

marcus_sommer — Mon, 03 Jun 2019 13:08:07 GMT

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