topic Re: expression not calculating correctly in QlikView

expression not calculating correctly

Tue, 14 Jul 2015 14:52:50 GMT

I have been playing around with the following expression to get it to calculate correctly but I cannot get it to behave as I expect. It doesn't matter where I put the brackets it gives me an error in expression. If I leave it as is it is calculating the second part of the expression first.

All 3 part work as individual expressions.

I can add the 2 QTY together without issue and divide one of the 2 QTY figures with the variable.

I want to add the 2 QTY figures together then divide by the variable but what is actually happening is the PICK QTY is getting divided by the variable and then added to the GRN QTY.

=num(Sum ({$<Type={GRN},PONumber-={"*TRANSIT"}>}QTY)+sum({$<Type={PICK}>}QTY)/(vTotalManHours-(vHolidays+vSickness+vHospDocsDentist+vAuthorisedAbsence+vUnauthorisedAbsence+vOvertime+vAgency)),'#,##0.00')

How can I fix this so it adds first then divides?

Thanks Steve

Re: expression not calculating correctly

sunny_talwar — Tue, 14 Jul 2015 14:55:37 GMT

May be this:

=Num((Sum({$<Type={GRN}, PONumber-={"*TRANSIT"}>}QTY) + Sum({$<Type={PICK}>}QTY)) /

(vTotalManHours- (vHolidays + vSickness + vHospDocsDentist + vAuthorisedAbsence + vUnauthorisedAbsence + vOvertime + vAgency)), '#,##0.00')

Re: expression not calculating correctly

alexandros17 — Tue, 14 Jul 2015 14:56:29 GMT

try this

=Num(Sum({$<Type={'GRN'},PONumber={'*'}-{'*TRANSIT'}>}QTY)+sum({$<Type={'PICK'}>}QTY)/(vTotalManHours-(vHolidays+vSickness+vHospDocsDentist+vAuthorisedAbsence+vUnauthorisedAbsence+vOvertime+vAgency)),'#,##0.00')

Re: expression not calculating correctly

MK_QSL — Tue, 14 Jul 2015 14:57:17 GMT

=num((Sum({$< Type={GRN},PONumber-={"*TRANSIT"}>}QTY)+sum({$<Type={PICK}>}QTY))
		/

(vTotalManHours-(vHolidays+vSickness+vHospDocsDentist+vAuthorisedAbsence+vUnauthorisedAbsence+vOvertime+vAgency)),'#,##0.00')

Re: expression not calculating correctly

Kushal_Chawda — Tue, 14 Jul 2015 15:03:17 GMT

num((Sum ({$<Type={GRN},PONumber-={"*TRANSIT"}>}QTY)+sum({$<Type={PICK}>}QTY))/(vTotalManHours-(vHolidays+vSickness+vHospDocsDentist+vAuthorisedAbsence+vUnauthorisedAbsence+vOvertime+vAgency)),'#,##0.00')

Re: expression not calculating correctly

Tue, 14 Jul 2015 15:03:57 GMT

Thanks everyone. I was in danger of putting brackets everywhere but where they needed to go.

Re: expression not calculating correctly

Tue, 14 Jul 2015 15:05:16 GMT

Hi Alessandro

unfortunately this gives me the same answer as I was getting which is incorrect.

thanks anyway.

Steve