Cross-Tab with Combining Formula

[theshadowknz]

I believe my situation is similar to thread767-1136986. I have a cross-tab that is giving me the status of records in a special sort order (@my order) that I made; and I want only one status, that is DISPOSED records for the last year only. Here is what I have so far; the last else if gives me the status DISPOSED field twice and both entries carry different results--none of which is correct. For instance the first is a total of 18 and the second is over 4,000--when I know the count should be just over 1800.

Here is the current formula that I have to get the correct sort order:

//@my_order
if {AST_Asset.Status} = "Ordered" then 1
else if {AST_Asset.Status} = "Received" then 2
else if {AST_Asset.Status} = "Being Assembled" then 3
else if {AST_Asset.Status} = "Deployed" then 4
else if {AST_Asset.Status} = "In Inventory" then 5
else if {AST_Asset.Status} = "Reserved" then 6
else if {AST_Asset.Status} = "On Loan" then 7
else if {AST_Asset.Status} = "Transferred" then 8
else if {AST_Asset.Status} = "Down" then 9
else if {AST_Asset.Status} = "In Repair" then 10
else if {AST_Asset.Status} = "End of Life" then 11
else if {AST_Asset.Status} = "Delete" then 12
else if {AST_Asset.Status} = "Return to Vendor" then 13
else if {AST_Asset.Status} = "Disposed" then 14

I then tried this formula to get the additional time frame:

//@my_order
if {AST_Asset.Status} = "Ordered" then 1
else if {AST_Asset.Status} = "Received" then 2
else if {AST_Asset.Status} = "Being Assembled" then 3
else if {AST_Asset.Status} = "Deployed" then 4
else if {AST_Asset.Status} = "In Inventory" then 5
else if {AST_Asset.Status} = "Reserved" then 6
else if {AST_Asset.Status} = "On Loan" then 7
else if {AST_Asset.Status} = "Transferred" then 8
else if {AST_Asset.Status} = "Down" then 9
else if {AST_Asset.Status} = "In Repair" then 10
else if {AST_Asset.Status} = "End of Life" then 11
else if {AST_Asset.Status} = "Delete" then 12
else if {AST_Asset.Status} = "Return to Vendor" then 13
else if {AST_Asset.Status} = "Disposed" then
if {AST_Asset.Disposal-Date} >= DateAdd("y", -1, CurrentDate) then 14
else
15

and I tried this in case I needed a string conversion:

else if {AST_Asset.Status} = "Disposed" then
if DateValue({AST_Asset.Disposal_Date}) >= DateAdd("y", -1, CurrentDate) then 14
else
15

Same results. I am using v9.

 

[lbass]

I think you need to test your data by laying it out in the detail section. I wonder if you have data entry errors where spaces may be added or something. Try putting the status field in the details, along with your formula and note where it is evaluating differently than expected.

Also, for the dateadd portion, you should change the "y" to "yyyy". "y" evaluates as dayofyear, not year.

-LB