Report - Aged by Date

[davidarcher]

I'm having problems compiling a report which will age a group of numbers by a given group of months on a rolling basis.

(IE latest 6 mths individually, then a grouping headed 7 - 12 mths, another 1 yr+, another 2 yr+)

Of course as the report runs month on month, the figures will move into gradually older sectors.

Any ideas on how this can be done automatically please?

 

[Pat Hartman]

Create a function that puts the date into the appropriate aging bucket and use the function in the query.

Public DetermineAgeBucket(InDate as date) as Integer
Dim AgeInMonths as Integer
AgeInMonths = DateDiff("m",Date(),InDate)
Select case AgeInMonths
Case < 7
DetermineAgeBucket = AgeInMonths
Case < 13
DetermineAgeBucket = 7
Case < 19
DetermineAgeBucket = 8
Case < 30
DetermineAgeBucket = 9
Case Else
DetermineAgeBucket = 10
End Select
End Function

Then in the query:

Select YourDate, DetermineAgeBucket(YourDate) As AgeBucket, YourAmt, ...

Then the report can sort and group by AgeBucket or you can create a crosstab query.

If you prefer, the case statement can return a string rather than a number. I chose a number so that you could sort the data if you needed to. If you want to display a text string instead of the aging bucket number, you can use the Choose() function.

=Choose(AgeBucket,"<= 6 months", "7-12 months", "<= 18 months", "<= 30 months", "other")

 

[davidarcher]

Pat,
thanks for your help - unfortunately I'm playing the thicky here!

Where do I use the information you have provided - how do I create a function etc? Is this in Visual Basic?

Help would be appreciated.

Thanks,

Dave UK

 

[GlennG]

Hi Pat,

First of all this a really old topic (found it on google) and thanks it really worked nice! I used it a bit different but got my results quite nice.

Just have a small question on the crosstab (never used it before).

I have my data as follow:

Customer - Bucket - Amount
2100 - 1 - 500
2100 - 2 - 300
2100 - 2 - 1000
2100 - 3 - 200
2101 - 1 - 200
2101 - 3 - 400
... and many more

It should result in:

Customer - Bucket 1 - Bucket 2 - Bucket 3... up to 12
2100 - 500 - 1300 - 200
2101 - 200 - 0 - 400
... many more

Challenges
- red: sum up the amounts in the same bucket
- green: give a 0 where the bucket is not existing

Already many thanks in advance

 

[GlennG]

Ahhhh, found it

red = just group my rows as customers and buckets as columns
green = put in
field: SumOfS_AMNTBAS: nz(Sum(S_AMNTBAS]);0)
total: Expression