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?

 

[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