Convert 0 to NULL

[Former Member]

Yes you're reading it correctly

Most people would like to convert NULL to 0. I need it the other way.

I made a formula which calculates an average. The 'average formula' is composed of other formulas (manual running totals).

Then I added this 'average formula' to a chart (line). Works nice.

However, when the value drops to 0 for a certain day (group by Date), the line drops to 0 in the graph. I don't want that.

Any average of 0 means "no data". So I would like to have the line broken for that day.

Chart options (right click on chart then check option [Break Lines for Missing Data]. But doesn't work when the value is 0.

So I need to write a formula which returns a NULL instead of a 0, so I can use the Break Lines option.

1) Does anyone have a solution for me?

2) Or does anyone have a trick to break line when value is 0?

[Former Member · Accepted solution]

Try this and see if it helps.

1. Create a formula with nothing in it. Just create a formula and then "save and close".

2. In the second formula say IF = 0 THEN {@FORMULA1} ELSE

I have tested it and it does test positive for NULLS, so it might just work for you.

[Former Member · Accepted solution]

Yes I tried the empty formula too. However, it returns 0 too instead of a NULL.

I disabled converting NULL values to default and such. But still no go.

By the way, thanks for all the help here.

Edited by: Valmont on Dec 3, 2009 9:53 PM

[Former Member · Accepted solution]

This may be "way out there", but it may work for you... (Also, this is a guess; it may not work...)

Do a left outer join between your current query and a (preferrable small) table with a number field. Join the two on a key you know will never, ever match, so the new number field always comes back null. Then return that field in your formula if the average is zero.

As an SQL example:

select stuff, for, report, s.store_number
from whereever w
left outer join stores s on (0 - w.quantity) = s.store_number
  -- quantity and store number are both always positive so 
  -- s.store_number will always be null, but still typed numeric.

And in your average formula (basic syntax):

if average({w.quantity}, {w.part}) = 0 then
  formula = {s.store_number}
else
  formula = average({w.quantity}, {w.part})
end if

My reasoning here is that the formula editor will require a numeric value to be returned for the average, so your NULL will have to be "typed" as numeric. This is the only way I could think of to get a numerically typed NULL.

Really just "thinking out loud"...

HTH,

Carl

[Former Member · Accepted solution]

Reply to you. You were close. I give you two points. We had the same idea but conversion of NULL to Val() we both forgot

Thanks again.

[Former Member · Accepted solution]

Thanks to you both. I had exactly the same problem (with averages). All fix now. Andrew

[Former Member]

Reply to myself as I solved it.

Pointing towards an empty formula IS the trick, however, convert te NULL-formula to a number (Val() ).

Example:

If {Data_.Amount} = 0 then Val({@toNull}) else {Data_.Amount};

See the Val() function? That one was missing. Works like a charm. Now the option to break the line in charts with No Data works as well.

However, thank you all for your efforts! Always good to be here.

Edited by: Valmont on Dec 4, 2009 6:05 PM

[Former Member]

if = 0 then " "

[Former Member]

That's not a NULL but an empty string. I can't use an empty string because the function needs to return either a number or a real NULL.

I know there's a trick to let a formula return a NULL by force but don't know how.

[Former Member]

it depends on how you are handling null values if nulls are empty strings in the database use the " "

if not

use

if field = 0 the isnull (simple as that)