Account Ageing load statement

[#1]

Hi,

I would really appreciate some help with the following:

In my load statement i have accounts outstanding that i am trying to determine the days outstanding. I have a release date when the invioce went out and a deptorAgedate when the statement was run. But i also have a field in the table Amount outstanding. If this field Amount outstanding is 0 the account is paid in full and if this field is not 0 i must calculate the days outstanding by substracting release date from deptorAgedate to get the days outstanding. Below is my load statement but for some reason every record returns 'Paid Full' even if debtoroutstandingamount is not 0 ?

if( (( (Date([DebtorAgeDate],'DD/MM/YYYY')) - (Date([DebtorReleaseDate],'DD/MM/YYYY')) < 30 ) and (DebtorOustandingAmount <> 0)),'Current'
,if( (( (Date([DebtorAgeDate],'DD/MM/YYYY')) - (Date([DebtorReleaseDate],'DD/MM/YYYY')) < 60 ) and (DebtorOustandingAmount <> 0)),'30 Days'
,if( (( (Date([DebtorAgeDate],'DD/MM/YYYY')) - (Date([DebtorReleaseDate],'DD/MM/YYYY')) < 90 ) and (DebtorOustandingAmount <> 0)),'60 Days'
,if( (( (Date([DebtorAgeDate],'DD/MM/YYYY')) - (Date([DebtorReleaseDate],'DD/MM/YYYY')) < 120 ) and (DebtorOustandingAmount <> 0)),'90 Days'
,if( (( (Date([DebtorAgeDate],'DD/MM/YYYY')) - (Date([DebtorReleaseDate],'DD/MM/YYYY')) < 150 ) and (DebtorOustandingAmount <> 0)),'120 Days'
,if( (( (Date([DebtorAgeDate],'DD/MM/YYYY')) - (Date([DebtorReleaseDate],'DD/MM/YYYY')) > 150 ) and (DebtorOustandingAmount <> 0)),'150 Days'
,'Paid Full')))))) as AccountAgeing,

Regards

Louwrie

[#2]

Try making a dummy field: Date([DebtorAgeDate],'DD/MM/YYYY') - Date([DebtorReleaseDate],'DD/MM/YYYY') As AgingDays. Then check the values of that field and make sure you are getting the values you would expect/

Since, "every record returns 'Paid Full' even if debtoroutstandingamount is not 0," that seems to suggest that the debtoroutstandingamount clause is not the problem, so the dates must be. It could be formatting.

If that doesn't help, we may need more information on what your data looks like. I didn't see anything out of place in your expression.

[#3]

yes it is a dateformat issue, i did create the dummyfield and its not returning anything.

Now to figure out how to fix the format - any suggestions ?

Thanks so much for your help.

Regards

Louwrie