Not show negative "-" from DateDiff

[fredong]

I am using CR v.8.0 and I do not want to show the negative "-" on the result but I still need the result. I have tried the Abs and it return 0 data and I tried subsrcipt and I ran into error like "This text does not appear to be apart of..". Please advise. Thanks. Below is my example.

//tableA.SalesDate = 05/11/05

Datediff("D",CurrentDate,{tableA.SalesDate})& "Days"

Result = -2.00Days
Desire Result = 2.00Days

 

[synapsevampire]

If what you tried didn't work (not sure why), then try:

if Datediff("D",CurrentDate,{tableA.SalesDate}) < 0 then
(Datediff("D",CurrentDate,{tableA.SalesDate}))*-1& "Days"
else
Datediff("D",CurrentDate,{tableA.SalesDate})& "Days"

I used:

abs(datediff("d",currentdate,currentdate-1)) & " hi"

And it worked fine...

-k

 

[fredong]

I have tried both of your syntax and it did not work for me
here is my original formula


if {tableA.CreateDate} < CurrentDate
and {tableA.ShipDate} < CurrentDate
and {tableA.UpdateDate} < {@CutoffDate}
and CurrentDate > {@CutoffDate}
then Datediff("d",CurrentDate,{tableA.SalesDate}) & "Days"


your first formula and results that I have tried.

if {tableA.CreateDate} < CurrentDate
and {tableA.ShipDate} < CurrentDate
and {tableA.UpdateDate} < {@CutoffDate}
and CurrentDate > {@CutoffDate}
then Datediff("d",CurrentDate,{tableA.SalesDate})* -1 & "Days"

Results = no data row return


your second formula and results that I have tried


if {tableA.CreateDate} < CurrentDate
and {tableA.ShipDate} < CurrentDate
and {tableA.UpdateDate} < {@CutoffDate}
and CurrentDate > {@CutoffDate}
then abs(Datediff("d",CurrentDate,{tableA.SalesDate})) & "Days"

Results = no data row return

 

[synapsevampire]

Ahhh, you originally stated that it returned 0, not that it didn't return data, you ned to read up on null vs. zero, they are NOT the same things, so you'll throw people off.

What you're probably experiencing is the formula falling out as a result of a NULL.

BTW, your original formula didn't even attempt to handle negatives as posted.

Use:

if not(isnull({tableA.SalesDate})) then
abs(datediff("d",currentdate,{tableA.SalesDate})) & " Days"
else
"0 Days"

Odd that one would want negatives reflected the same as positives.

-k

 

[fredong]

sorry about the misinformation, the formula only search for negative and I already negate out the postive in the select expert. BTW how do I merge your formula into my formula? Can you help. thanks

if {tableA.CreateDate} < CurrentDate
and {tableA.ShipDate} < CurrentDate
and {tableA.UpdateDate} < {@CutoffDate}
and CurrentDate > {@CutoffDate}
then abs(Datediff("d",CurrentDate,{tableA.SalesDate})) & "Days"

merge
if not(isnull({tableA.SalesDate})) then
abs(datediff("d",currentdate,{tableA.SalesDate})) & " Days"
else
"0 Days

 

[synapsevampire]

Try:

if {tableA.CreateDate} < CurrentDate
and {tableA.ShipDate} < CurrentDate
and {tableA.UpdateDate} < {@CutoffDate}
and CurrentDate > {@CutoffDate} then
abs(datediff("d",currentdate,{tableA.SalesDate})) & " Days"
else
"0 Days"

-k

 

[fredong]

Results = no data row return

 

[synapsevampire]

Try selecting the Report-Options->Convert null data to default.

You have many fields which might be producing a null.

Where are you placing this formula?

-k

 

[fredong]

I have made the changes selecting the Report-Options->Convert null data to default and no data row return..

I am placing this formula in a Group section.

If it does not work do not worry about it.

 

[CR85user]

Might be best to test for nulls 1st to take those out of the equation.

As synapsevampire pointed out you may have many fields each generating null values which may affect the output - Also the way in which the tables are linked could have an effect on the output.

Consider using:

if isnull({table.fieldname}) to test for null values on each 1st as part of the formula criteria

Might be best to just check 1st by doing a test report limiting the selection criteria to isnull({table.field}) to see which fields return a true null value / 0 value / '' value