topic Re: Sum Function Not working Properly in QlikView

Sum Function Not working Properly

Shiva123 — Mon, 27 Nov 2017 17:12:53 GMT

hey ,

everyone i have this issue which is a simple expression, at the designer end (Front end) is its working fine but at the script level it is not doing the exact calculation as the front end . The calculation here is Amount / Quantity, where in some of the Amount's are negative and some of the Quantity are Zero's, my question is here is these two factors are causing the problem is there any way t resolve this or a better way to write the expression in script.

Thanks,

Shiva

Re: Sum Function Not working Properly

eduardo_dimperio — Mon, 27 Nov 2017 21:12:23 GMT

Well, if i understand rightly you have this situation:

Amount/Qunatity

Ex:

Produt A: 12/4 + 6/0

Produt B: 30/10 + (-20)/5 + (-3)/0

If so, you can just replace when Quantity = 0 to =1, about negative Amount value i dont think this is a problem.

Re: Sum Function Not working Properly

Shiva123 — Mon, 27 Nov 2017 21:15:23 GMT

your example is right .. and where to replace the quantity?

Re: Sum Function Not working Properly

woshua5550 — Tue, 28 Nov 2017 08:04:44 GMT

maybe this ?

Load

IF(Quantity=0,0, Amount / Quantity) as Price

From <TableName>

Re: Sum Function Not working Properly

antoniotiman — Tue, 28 Nov 2017 08:51:27 GMT

Try this

Alt(Amount/Quantity,0) as NewField

Re: Sum Function Not working Properly

eduardo_dimperio — Tue, 28 Nov 2017 12:30:19 GMT

You can use something like this my friend:

yourtable:

load

field1,

field2,

amount

If(quantity=0,1,quantity) as quantity

from yourqvd.qvd

or in one step like sugest Dave in comment above.

Re: Sum Function Not working Properly

Shiva123 — Tue, 28 Nov 2017 16:36:08 GMT

tried this one didn't work .. my expression was like this (if(QTY =0,0,sum(Amount)) /Sum(QTY))as [New Filed] .

is there any thing wrong in my expression? Could You Please Help me out .

Thanks

Shiva

Re: Sum Function Not working Properly

Anil_Babu_Samineni — Wed, 29 Nov 2017 00:44:11 GMT

One extra parenthesis closed in advance after first aggregation sum. Try this

(if(QTY =0,0,sum(Amount) /Sum(QTY))as [New Filed]

Re: Sum Function Not working Properly

Shiva123 — Wed, 29 Nov 2017 00:47:36 GMT

this expression is saying wrong ..

Re: Sum Function Not working Properly

Anil_Babu_Samineni — Wed, 29 Nov 2017 00:51:44 GMT

What do you mean wrong? Throwing any error or returning nothing..

Re: Sum Function Not working Properly

Shiva123 — Wed, 29 Nov 2017 01:02:26 GMT

throwing an error for Parenthesis, so i did like this

(if(QTY =0,0,sum(USD) /Sum(QTY)))as [NEW FIELD]

Re: Sum Function Not working Properly

Anil_Babu_Samineni — Wed, 29 Nov 2017 01:07:01 GMT

Expression seems okay, Now share full script or what you are trying?

(if(QTY =0,0,sum(USD) /Sum(QTY))) as [NEW FIELD]

Re: Sum Function Not working Properly

sunny_talwar — Wed, 29 Nov 2017 01:28:40 GMT

May be try this

Alt(Sum(USD)/Sum(QTY), 0) as [NEW FIELD]

Or this

If(QTY = 0, 0, Sum(USD)/Sum(QTY)) as [NEW FIELD]