topic Re: Sum-function if ... in QlikView

Sum-function if ...

dominicanlauf — Sat, 16 Nov 2024 20:30:17 GMT

Hello everybody,

i want to sum up all defective products in the production. To determine that an order is defective, the description "CANCELED" must be assigned to the order number. If an error exists, a 1 is stored for this cause of the error. Then 1 is to be multiplied by the number of pieces produced.

My formula:

sum({<MPS_productionorderitem.state={CANCELED}>}(MPS_productionorderitem.axisdefect*(MPS_productionstatistic.ok)))

But it doesn't work. Does anybody has an idea?

best regards,

Dominic

Re: Sum-function if ...

gf — Mon, 08 Jul 2019 11:51:27 GMT

sum({<MPS_productionorderitem.state={CANCELED}>}(MPS_productionorderitem.axisdefect*(MPS_productionstatistic.ok)))

Are you missing a " ) "?

Re: Sum-function if ...

dominicanlauf — Mon, 08 Jul 2019 13:15:23 GMT

No, unfortunately not.

regards,

Dominic

Re: Sum-function if ...

lockematthewp — Mon, 08 Jul 2019 14:09:02 GMT

I believe you need single quotes around CANCELED. So:

sum({<MPS_productionorderitem.state={'CANCELED'}>}(MPS_productionorderitem.axisdefect*(MPS_productionstatistic.ok)))

Re: Sum-function if ...

gf — Mon, 08 Jul 2019 14:13:44 GMT

SUM( { <MPS_productionorderitem.state={'CANCELED'} > } MPS_productionorderitem.axisdefect*MPS_productionstatistic.ok)

Re: Sum-function if ...

dominicanlauf — Fri, 12 Jul 2019 06:36:46 GMT

I thought the same but it does not work.

The formula only works if there isn't the condition MPS_productionorderitem.state={'CANCELED'}.

If the condition is active the result is always 0.

Do you have another idea?

Re: Sum-function if ...

jensmunnichs — Fri, 12 Jul 2019 11:30:49 GMT

I think the quotes do need to be there, but I think the brackets aren't quite right either. Since you don't close the brackets after 'MPS_productionorderitem.axisdefect', I think it's trying to apply the set analysis to both MPS_productionorderitem.axisdefect and MPS_productionstatistic.ok, and I'm not sure whether that's possible or what you want to be doing here.

On the other hand, if your requirement is simply something along the lines of 'sum the amount of items that were on an order that got cancelled', if your data model is correct, shouldn't you just be able to do something along the lines of Sum( { <MPS_productionorderitem.state={'CANCELED'} > } <ProductionAmountField>)?

Sorry if I'm misunderstanding your requirement or data model, would you be able to upload your application (if necessary with dummy data)? That would make it a lot easier to find a solution for you.

Re: Sum-function if ...

dominicanlauf — Fri, 12 Jul 2019 12:28:19 GMT

Samples will be taken for each order. If the samples are at a certain error, a "1" for the error will be deposited (for example, 'MPS_productionorderitem.axisdefect '= 1). This statement just states that there is an error. If the job is additionally 'CANCELED', it means that the error is multiplied by the number of pieces produced ('MPS_productionstatistic.ok') because that number of products has the error.

Is it more understandable now?

I posted an example: https://community.qlik.com/t5/QlikView-Creating-Analytics/Sum-function-under-condition/m-p/1601723#M47308

Re: Sum-function if ...

jensmunnichs — Fri, 12 Jul 2019 14:26:56 GMT

So are you not just looking for the sum of number of pieces produced where [....defect] = 1 and [...state] = CANCELED? In which case, why not just add both to the set analysis?

In your application, I added a text object with the formula below, and I'm getting 1325 as a result (I used bcdefect instead of axisdefect as your original query will always result in 0 as you discovered in the other thread), which is correct as far as I can tell.

=SUM({<MPS_productionorderitem.bcdefect={1}, MPS_productionorderitem.state={'Canceled'}>} MPS_productionstatistic.ok)

Your solution does actually seem to work, I personally just feel that adding both to the set analysis makes the formula a lot easier to read.