Comparing Month End Change

[ThomasLafferty]

Hi there -
I need to be able to compare the percentage change for amounts over due (>60 Days, >90, >120).

The table in which the data resides is Current Month Aging History.

Here is the field list:
CUSTNO
NAME
DATE
OVER60
OVER90
OVER120

Here is the query I am currently using:

SELECT [Current Month Aging History].CUSTNO, [Current Month Aging History].NAME, [Current Month Aging History].DATE, [Current Month Aging History].OVER60, [Current Month Aging History].OVER90, [Current Month Aging History].OVER120, [Current Month Aging History].SALESREP
FROM [Current Month Aging History]
WHERE ((([Current Month Aging History].DATE)=DMax("[Date]","Current Month Aging History","Month([Date]) = Month(Date()) And Year([Date]) = Year(Date())") Or ([Current Month Aging History].DATE)=DMax("[Date]","Current Month Aging History","Month([Date]) = Month(Date())-1 And Year([Date]) = Year(Date())")))
ORDER BY [Current Month Aging History].NAME, [Current Month Aging History].DATE DESC;

Here is what the output looks like:

[b]CUSTNO     NAME     DATE     OVER60     OVER90     OVER120[/b]
999999     FRED   09/30/07     0.00     35.75     0.00
999999     FRED   08/31/07     35.75    0.00     0.00
888888     WORM   09/30/07     0.00     0.00     0.00
888888     WORM   08/31/07     0.00    0.00     0.00

In the report I would like to run from this, I need to show percentage change. Additionally, I would like a way of sorting it so that the worst offenders are first (i.e. last month, they had nothing over 60, now they have significant over 60).

Sorry for not being a bit clearer on this!

Born once die twice; born twice die once.

 

[SkipVought]

Hi,

So you want a % change.

% change with respect to WHAT?

Last Month's value? Last month's change value?

In any case, you would have to have stored those values in order to make the calculation.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[JerryKlmns]

Thomas

I would create a crosstab query on DATE field (bad name though for a table field!), making sure you have all months from Jan-Dec(sorted) of all(?) years for every customer. Then query this to get % change on overdue amounts.

About worst offenders, sorted by total amount due or bucket and amount due?

 

[ThomasLafferty]

Thanks for the reply! This sounds like a decent way to go, and I will try it on Monday. As for which way to sort it, I will have to hash that out with the collections manager.

I agree that the DATE field is an absolutely bad idea, but I am stuck with it - it's an export from a legacy database.

Suggestions on querying the query for % change?



Born once die twice; born twice die once.

 

[JerryKlmns]

I think that % change on due amounts should be heavy for each customer, plus if you need to do that for customer' s bucket it comes out very complicated. If you do that for total amount due? In your example FRED has a -100% for Bucket60 and how is it defined for bucket90?

My version of bucket evolution holds static all deliquency data of every account at a fixed time like Billing date (happens on the 1st of the month) and compares it with todays data. Therefor you can find change on Amount Due, Buckets, Loan Total Amount & Exposure of every account.
Since you plan to use the EOM data, I would freeze them on a not so normalized table (that is a "crosstab" ) and do calculations on fields. This is limited to one month
Maybe it would be a good choice to create such a table and do calculations on system update time. Then a smaller query could run faster. Make your choice and come back to work on it.