topic Incorrect Sum up in QlikView

Incorrect Sum up

Wed, 25 Nov 2020 16:16:04 GMT

Hello all,

I was working on a straight table in qlikview and came across this;

The total for Excess should be -264.61 but instead I'm getting 0.00

What could be the cause of this and how can I get the right figure?

Thanks

Fifi Nokoe

Re: Incorrect Sum up

MindaugasBacius — Tue, 19 Jul 2016 08:09:55 GMT

Try doing this:

You should remember that this option is able only in Straight table.

Re: Incorrect Sum up

Frank_Hartmann — Tue, 19 Jul 2016 08:09:55 GMT

can you share a sample?

Re: Incorrect Sum up

danansell42 — Tue, 19 Jul 2016 08:11:00 GMT

Hi Fifi

What is the expression you have used?

Is it possible the -264.61 is not actually formatted as a number?

Thanks

Dan

Re: Incorrect Sum up

sunny_talwar — Tue, 19 Jul 2016 10:08:42 GMT

What is the expression used here?

Re: Incorrect Sum up

susovan — Tue, 19 Jul 2016 10:37:43 GMT

Try this way,

I have used this below expression in this chart

Expression : if((Sum(Amount-Amount1))<0,'('& Sum(Amount-Amount1)& ')',Sum(Amount-Amount1))

Re: Incorrect Sum up

Tue, 19 Jul 2016 11:00:19 GMT

Hello Dan, Thanks for your response. This is the expression; IF(ov_ExpiryDate < TODAY(),COLUMN(1), IF(COLUMN(2)> FABS(COLUMN(1)),'', COLUMN(1) + COLUMN(2))) Column is money formatted and even when it's fixed to two decimal places it doesn't sum the column total

Re: Incorrect Sum up

nizamsha — Tue, 19 Jul 2016 11:00:54 GMT

create a 2 field and check what format you are getting.

if Your value(-264) is aligned in leftside then you will get 0 only

replace that open and close bracket in the back end and make that one as number like this

PurgeChar(Amount,'()') as Amount

then do ur calculation it should work

Re: Incorrect Sum up

Tue, 19 Jul 2016 11:06:12 GMT

column 1 >>>> sum(Balance) column 2 >>>>SUM(IF(LEFT(ID_ID,2)='LD',(Balance) * -1,IF(LEFT(id_ID,2)<>'LD',limit))) Excess >>>>>>>>>>>>> IF(expiry< TODAY(),COLUMN(1), IF(COLUMN(2)> FABS(COLUMN(1)),'', COLUMN(1) + COLUMN(2))) Column is money formatted

Re: Incorrect Sum up

nizamsha — Tue, 19 Jul 2016 11:58:17 GMT

can you share excel so that we can check where was that gap

Re: Incorrect Sum up

qlikviewwizard — Tue, 19 Jul 2016 12:23:23 GMT

Can you share the sample app with the data to demonstrate your issue?

Re: Incorrect Sum up

Wed, 20 Jul 2016 11:50:22 GMT

Nizam Thank you for your response. I found an alternative way to solve this by summing the expression in the edit script with a "group by" function. Your input though was duly appreciated.

Re: Incorrect Sum up

Wed, 20 Jul 2016 11:51:40 GMT

Thank you all for your responses. I found an alternative way to solve this by summing the expression in the edit script with a "group by" function. Your inputs and feedback's were all appreciated.