6 Replies Latest reply on Jul 31, 2018 12:05 PM by Chandrika Pai

# Not able to assign 0 to negative Values

Hi ,

I have a simple if condition to assign 0 for negative values.

But in my result set not all negative values are marked 0

For Example: there is negative value in the first row and should be assigned 0 but it is some random value. Not able to understand the tableau calculation. Please let me know if i am doing anything wrong. Any suggestion are really appreciated.

I cannot place my workbook as this is confidential.

Thanks

Chandrika Pai

• ###### 1. Re: Not able to assign 0 to negative Values

Can you share the calculation used for Difference?

• ###### 2. Re: Not able to assign 0 to negative Values

Hi Ken,

here is the difference:

• ###### 3. Re: Not able to assign 0 to negative Values

Hi Chandrika Pai

I've tested same data with my desktop and it correctly shown 0 when there is negative.

Better change your data like id, remove unwanted columns and extract with one twbx file and share here. we will work and let you know. some time the data type or coming from data source getting error.

Thanks

Sankarmagesh Rajan

Mob:+919894170397

BI, Aspire Systems (India) Pvt Ltd

• ###### 4. Re: Not able to assign 0 to negative Values

Chandrika,

You need to change your formula for difference calculation a bit.

SUM([PROV_ALWD_AMT]) - SUM([estimated rate])

Then use this for your "assign 0 for negative values calculation.

The issue is you are checking the difference at row level. So for example for your first row (NCD_CD ending with 3001) if you have 10 rows in your source, So the rows for which value is negative it will make 0 and other positive rows data it will add up . That is what random values you are seeing. For the NCD_CD for which all the values are negative in your source, you are seeing 0 only for those.

So you first need to aggregate it then find the difference.

Hope it helps.

1 of 1 people found this helpful
• ###### 5. Re: Not able to assign 0 to negative Values

In your calculated field, you are doing "if difference <= 0" but in the sheet you are doing "if the sum of all difference values <= 0" .... so Tableau is working properly but you're confusing a non-agg field "if difference" with an aggregated field "if SUM(difference)"

There's a few correct answers in this thread already, but the fastest might be to change your [assign 0 to negative values] field to use SUM around the difference.

1 of 1 people found this helpful
• ###### 6. Re: Not able to assign 0 to negative Values

Hi All,

Thank you all for answering this question. I realized that my calculation was in line level which was right. But to get 0 i should have aggregated it which I stupidly forgot. Thank you so much guys

Chandrika Pai

1 of 1 people found this helpful