topic Re: Sum function under condition in QlikView

Sum function under condition

dominicanlauf — Sat, 16 Nov 2024 20:28:16 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 under condition

Frank_Hartmann — Fri, 12 Jul 2019 07:21:14 GMT

Maybe this:

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

Re: Sum function under condition

dominicanlauf — Fri, 12 Jul 2019 07:27:28 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 under condition

Frank_Hartmann — Fri, 12 Jul 2019 07:41:18 GMT

not without looking into your qvw.

maybe you are able to scramble (settings -> Docproperties -> Scrambling) sensitive Fields and upload the qvw.

Or try to rebuild the issue with testdata and then upload the file!

Re: Sum function under condition

dominicanlauf — Fri, 12 Jul 2019 08:16:32 GMT

Maybe that will help.

thanks,

dominic

Re: Sum function under condition

Frank_Hartmann — Fri, 12 Jul 2019 08:59:13 GMT

As far as i can see you are getting the 0´s in F_Axis column because the field MPS_productionorderitem.axisdefect is 0 for all MPS_productionorderitem.state = CANCELED. So if you multiply by 0 you will get 0 as result!

Re: Sum function under condition

dominicanlauf — Fri, 12 Jul 2019 12:31:09 GMT

That's right, currently no error cause is assigned a "1". Accordingly, the problem must already lie in the basic data.

Many Thanks

Dominic