6 Replies Latest reply on Jun 7, 2018 10:17 PM by Sohan Jawar

# Converting Negatives and 0 values to 0

Its is so weird with my calculation. I have a field with values like this.

Name     Quantity     Type          Date

XX            -8               1          07052018

XX             8               2          07052018

XX             0               3           07052018

XX             -8             2           080502018

XX              8              4            08052018

XX              0              3            08052018

I have this negative values to consider as 0 and 0 values as 0. So i am trying to create a calculation like this:

If quantity <= 0 then 0

else quantity end

But this is giving me so weird results. It is converting negatives to 0 but 0 is somehow getting as 8 rather than 0. I have used round etc but didn't work for me.

Then i used

If sum(quantity) <= 0 then 0

else sum(quantity) end

or i have used (Currently i used this , because i do get only value as 8 or 0 or -8 mostly other values If any other values then fine i take it as other value only but my intention is if it is -ve and if it is 0 then 0 else quantity )

so i consider this as this is giving me exact value what i expected...

IF SUM([Quantity]) >= 8 THEN SUM([Quantity])

ELSE 0 END

It is giving me correct results. But i have a problem here, I need one more calculation which i cannot use this aggregation field there.

• ###### 1. Re: Converting Negatives and 0 values to 0

Hello Lalitha

Here It Is

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread

• ###### 2. Re: Converting Negatives and 0 values to 0

I dont know in my case it is so weird if -ve is working then 0 is not working if 0 is working then -ve is not working. But both cases are not working.

• ###### 4. Re: Converting Negatives and 0 values to 0

I cant attach the workbook but i have given the screenshot here.. Let me know if this doesn't help much..

In the first screenshot it is weird i am getting the values like 16 or 32 like this rather than 8

and in the second screenshot i am getting 8 instead of 0 which i highlighted in red.

SUM(CASE SIGN([Quantity])

//WHEN -1 THEN 0

WHEN 0 THEN 0

ELSE ([Quantity]) END)

But if consider like this i am getting exact values (like 0 is converting as 0 and other values as exact like others but i still need to consider -ve also as 0)

i tried to create 2 calculations but again same issue didn't work for me..

• ###### 5. Re: Converting Negatives and 0 values to 0

lalitha, you would appreciate that it won't be helpful. If you can't post then email me. I will have  a look.

thanks

Deepak

• ###### 6. Re: Converting Negatives and 0 values to 0

Hi Lalitha,

Use the IF condition without aggregating with Sum.

IF [Quantity]<=0 THEN 0

IF [Quantity]>0 AND [Quantity]<1 THEN 0   // This will allow you to make the values where the quantity is just >0 to 0 without using ROUND.

ELSE [Quantity] END

Regards

Sohan.