4 Replies Latest reply on Feb 23, 2016 11:46 PM by Mark Fraser

# My Calculated Field Doesnt Show Negative Numbers

Hi all,

I have two measures I created. Holiday Sales and Fall Sales.

I subtract them normall

Holiday Sales - Fall Sales

My Fall Sales are all numbers while Holiday is all 0.

Yet when I do up a chart of this, it doesn't show me negative values. Any reason why?

Sunil

• ###### 1. Re: My Calculated Field Doesnt Show Negative Numbers

Hey Sunil!

You got some screenshots? Are you sure holiday sales are 0 or are they null? If it is literally null, the calculation wouldn't operate the subtraction. Either way, you would be better off by giving us some sample data.

Pooja.

• ###### 2. Re: My Calculated Field Doesnt Show Negative Numbers

Hey Pooja,

There are nulls that I already converted to 0 using the LOOKUP(ZN(SUM([ Net Amt (USD)])),0)

However, I cant use the newly created calculated measure above to  create measures for Holiday and Fall separately.

• ###### 3. Re: My Calculated Field Doesnt Show Negative Numbers

So my initial measure is "Net Amt USD".

It has several nulls which is essentially 0.

So I used the function LOOKUP(ZN(SUM([ Net Amt (USD)])),0) to replace the nulls with zeros.

Then I tried to pull out just the Holiday Sales (Net Amt USD) using this calculation. IF [Season] = 'Holiday' THEN Net Amt USD (the new calculated field replacing the nulls with zero), but it doesn't work, the calculation shows an error.

However, if I pull out Holiday sales using the INITIAL Net Amt USD measure it works. But when I use the 2 newly created measures (Holiday Sales, Fall Sales) to minus off each other.

I try every way but my negatives show as 0.

• ###### 4. Re: My Calculated Field Doesnt Show Negative Numbers

Hi Sunil

Excuse the interruption...

Is there a reason you are using the LOOKUP() function? Its likely to have been set up wrongly, and therefore is affecting the rest of your calculation.

Something like this, should be sufficient - ZN(SUM([Net Amt (USD)]))

Otherwise, if you could mock up some basic numbers in Excel (to replicate the problem), it would be easier to help!

I can mock up an example, but i dont know if it will help.

Are you using 9.2.4?

Cheers

Mark