9 Replies Latest reply: Aug 28, 2014 11:00 AM by ARICE01 _

# Numeric Null

Hi,

I've a calculated field of type numeric.

I m using a formula on field-1 of char type

If([isnull[field-1],0,val()) is working fine. But  i m getting 0 if the value is null how can I display NULL(nothing) instead of 0 ??

Like this

If([isnull[field-1], NULL[/b] ,val())

(calculated field must be numeric type)

Thanks

• ###### Numeric Null

From

what settings do you have?

Grant

Originally posted by yesb:

Hi,

I've a calculated field of type numeric.

I m using a formula on field-1 of char type

If([isnull[field-1],0,val()) is working fine. But  i m getting 0 if the value is null how can I display NULL(nothing) instead of 0 ??

Like this

If([isnull[field-1], NULL[/b] ,val())

(calculated field must be numeric type)

Thanks /b[/quote]

• ###### Numeric Null

Hi

It depends on what you want.  If you want the absolute text NULL to display, then you will have to create a character calculated field with the if condition setting "NULL", if you want a true null in a numeric calculated field, as seems to be the case here, just have the if condition set 1/0 (a divide by zero error).  Then, how the null displays will be dictated by the editable setting that Grant referred to in Options|Display null values as:

Gareth

Originally posted by yesb:

Hi,

I've a calculated field of type numeric.

I m using a formula on field-1 of char type

If([isnull[field-1],0,val()) is working fine. But  i m getting 0 if the value is null how can I display NULL(nothing) instead of 0 ??

Like this

If([isnull[field-1], NULL[/b] ,val())

(calculated field must be numeric type)

Thanks /b[/quote]

• ###### Numeric Null

Thanks  Gareth,

1/0  Technique[/b] is worked fine.

• ###### Re: Numeric Null

All:

I am trying to calculate a Total across a number of numeric values, where certain values are Null.  When the calculation encounters a Null value, Modeler refuses to calculate the Total and renders the Total as Null.  I am sure there is a simple workaround. Can any one help me with this one?  I am coming up blank at the moment with a workaround.

Al

• ###### Re: Numeric Null

Hi Al,

Did you try what was posted above in Gareth's post if(isnull(field1),0,field1)?  Insert that formula for each field you are totalling up across and that should take care of nulls.

• ###### Re: Numeric Null

Hello Al,

You can clean up nulls in the table using the if(isnull)) trick, but you can find nulls creeping in to summaries where there's no data that matches your criteria, especially when using an across key. Your totals should still work though - are they calculated as a normal sum() measure?

Best wishes

Olly

• ###### Re: Numeric Null

Thanks RalphB... I guess I was being lazy and hoping there was a simpler way.

My current formula in Summary is:  Sum(Jan)+Sum(Feb)+Sum(Mar)+Sum(Apr)...etc, and in some cases a Month value is Null.  There are many Rows for each Month.   I was trying to avoid doing the IsNull() test on every Month.  I guess there is no way around this, since my Summary has both Column Totals and Row Totals.  So I guess I have to take the long road...

Thanks,

• ###### Re: Numeric Null

Hello Al,

Is your source data a report or a database? Surely it's easier to use multiple column region to have Month as a field in the table.

Best wishes,

Olly

Sent from my BlackBerry 10 smartphone.

• ###### Re: Numeric Null

Hi Olly:

The source data is an Excel file with complicated, embedded Hyperion query links.  I did not want to edit the Hyperion queries, as the Excel file is maintained by the corp finance group.  I am looking at my summary again, mysteriously, I believe the Sum() function is working now.  Not sure how the Summary calculation started to work.

I will confirm on this post later if all is well...

Thanks Olly and RalphB