Qlik Community

QlikView Publisher

Discussion Board for collaboration on QlikView Publisher.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

eliminate negative values in the report

While summing up null values the output should be zero but I am getting negative zero and some garbage values. Please help me how to get rid of such values.

for eg :

value + amount = null + null = -2.387E10

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: eliminate negative values in the report

Thanks guys for the replies!!

I resolved it by rounding off the values:

round(value,1) +  round(amount,1)

Its working correctly now.

9 Replies
salto
Valued Contributor II

Re: eliminate negative values in the report

Hi,

Before summing up, check if any of the elements is null. In such case, set 0 as the result.

Try something like:

if(not(isnull(Value) and not(isnull(amount),Value+amount,0)) as Result

Hth.

senpradip007
Valued Contributor III

Re: eliminate negative values in the report

Try like:

Sum(If(Len([Pre-Tax_Value_for_CY])>0,[Pre-Tax_Value_for_CY], 0) + If(Len([Pre-Tax_Value_for_LY])>0,[Pre-Tax_Value_for_LY], 0))

MVP
MVP

Re: eliminate negative values in the report

If the amount and value you mentioned are fields, you could simply use RangeSum(), like:

RangeSum(amount, value)

Re: eliminate negative values in the report

or you can even use FABS() function.

Regards

ASHFAQ

Not applicable

Re: eliminate negative values in the report

rangesum and  isnot null is working fine for null values....but while summation is not working for all the scenarios like :

1+-1 =0

instead it is giving 1+ -1 = -2.345E10

Not applicable

Re: eliminate negative values in the report

fabs doesn't give me correct results

MVP
MVP

Re: eliminate negative values in the report

It could be decimal precision issue. What you see, 1/-1 might be those are actually something with more decimal pionts like:

+/-0.999991211  or so. Load the numeric data and change the number format in the number tab to check the actual value it is being loaded as.

Sokkorn
Honored Contributor

Re: eliminate negative values in the report

You may try NumSum(value + amount)

Not applicable

Re: eliminate negative values in the report

Thanks guys for the replies!!

I resolved it by rounding off the values:

round(value,1) +  round(amount,1)

Its working correctly now.

Community Browser