Calculate the date diff

[Former Member]

Hi, The requirement is to calculate the diff between 2 date fields and display it. I could able to get the difference between the 2 date fields. But now the result column SHOULD DISPLAY the result ONLY if the 2 date fields have the date set ELSE it should be left blank(basically NULL shud be set to the result column in such cases).

The formula which I have used to get the result is :

(NOERR(date1)<>0 AND NOERR(date2)<>0)*(date1-date2).

The above formula works fine and gives the result as desired except that it puts "0" even though the date fields doenst have a value.I have tried to use the ELSE part by giving "1/0" thinking it wud give me "#" but this didnt help me out anyway.

Any inputs on this...

[Former Member]

would it be acceptable to set it to a special value (say 999) if one or both dates are incorrect? You can formulate accordingly.

[Former Member]

Thanks for the response.

That was an option which I also thought of and I wud use it only as a last rescue and trying if I can get some info to do it otherwise.

The other option which I have in my mind is to just get this diff into the cube and this shud resolve but then again I hv to chg the BW structures and unnecessarily I wud be increasing the load to the cube.

Any other option in Bex ...???

[Former Member]

What do you want to set it to, in case one of the dates is incorrect?

You can set it to that date (one that is incorrect), not sure if that would bring a '#'.

[Former Member]

I dont want to set it to any value and infact I tried that option too but it doenst bring up "#" and instead it shows "0".

I'm not able to default this formula to NULL either.

Suppressing "0" also didnt help me out...