Chart not working properly

[Dukester0122]

I have a formula that shown below with results measured in days delayed for shipments:
if {?Performance between} = "Order to Invoice Date" then
( if {AVSOPHDR.ORDRDATE} = Date(1900, 1, 1) then "No BORD/DORD"
else totext({AVSOPHDR.DOCDATE}-{AVSOPHDR.ORDRDATE},0,""))
else if {?Performance between} = "Order to Actual Ship Date" then ( if {AVSOPHDR.ACTLSHIP} = Date(1900, 1, 1) then "Date Not Entered" else totext({AVSOPHDR.ACTLSHIP}-{AVSOPHDR.ORDRDATE},0,""))

I created a chart then sorted the results of @MeasureDates with the following:
On-time less than or equal to 0
One Week 1 to 7 days
Two Weeks 8 to 14 days
Three Weeks 15 to 21 days
More than 4 weeks More than 22 days

Only On-time, One week and More than 4 weeks are showing in the chart. i've checked the results in the report and a lot are in 2 and 3 weeks.

 

[lbass]

Do your dates appear as date(1900,1,1) when you place them in the detail section? Or are they actually null in some cases?

-LB

 

[Dukester0122]

they show up as dates(1900,1,1)

 

[lbass]

What is the formula you are using to sort the results into the weeks intervals?

-LB

 

[Dukester0122]

I created a Chart Sort Order shown on my first email.

 

[lbass]

I see. You used the selection option within the specified order screens to set up these intervals. This results in the same thing as when you create a formula like:

if {@MeasureDates} <= 0 then "On-time" else
if {@MeasureDates} in 1 to 7 then "One Week" else
if {@MeasureDates} in 8 to 14 then "Two Weeks" else
if {@MeasureDates} in 15 to 21 then "Three Weeks" else
if {@MeasureDates} > 22 then "More than Three Weeks"

I think the problem could be with results that fall between intervals, e.g. 7.5 days, 14.3 days, etc. I would try rounding {@Measuredates} by creating this formula:

if round({@MeasureDates}) <= 0 then "On-time" else
if round({@MeasureDates}) in 1 to 7 then "One Week" else
if round({@MeasureDates})in 8 to 14 then "Two Weeks" else
if round({@MeasureDates}) in 15 to 21 then "Three Weeks" else
if round({@MeasureDates}) > 22 then "More than Three Weeks"

Use this formula as your "on change of" field in the chart. You can still use specified order to get the right display order. This should work.

-LB