Due Date Buckets

building reports desktopintelligence
1

Dear Sandra:
Instead of variables, you may wish to create objects using decodes as in
the following actual example that works OK in our Oracle AcctsReceivable
system:

Object Name: Amt 1-30 Days Past Due
decode(round((sysdate-AR_PAYMENT_SCHEDULES.DUE_DATE)/30 + .5
),1,AR_PAYMENT_SCHEDULES.AMOUNT_DUE_REMAINING,0 )

Object Name: Amt 31-60 Days Past Due
decode(round((sysdate-AR_PAYMENT_SCHEDULES.DUE_DATE)/30 + .5
),2,AR_PAYMENT_SCHEDULES.AMOUNT_DUE_REMAINING,0 )

Object Name: Amt 2-3 Years Past Due
decode(round((sysdate-AR_PAYMENT_SCHEDULES.DUE_DATE)/360 + .5
),3,AR_PAYMENT_SCHEDULES.AMOUNT_DUE_REMAINING,0 )

Object Name: Amt Over 3 Years Past Due
AR_PAYMENT_SCHEDULES.AMOUNT_DUE_REMAINING

decode(round((sysdate-AR_PAYMENT_SCHEDULES.DUE_DATE)/1080 + .5
),1,AR_PAYMENT_SCHEDULES.AMOUNT_DUE_REMAINING,0)

Sincerely, Tom Slone
Lawrence Livermore National Lab
Livermore, California

Sandra wrote:

I have a basic accounting table which tracks payments made and payments due
for an account with a specific due date. There are several entries for each
account, and I am wanting to sum the balance due/paid where the
days delinquent (today - date due) falls within several ‘buckets’. Days
delinquent is a measure that I created.

Example Results
Account 1 $25.00 10 days 1-30 30-60 60-90
Account 1 $25.00 20 days Account 1 $50.00 $25.00 $25.00
Account 1 $25.00 35 days
Account 1 $25.00 65 days

Listserv Archives (BOB member since 2002-06-25)

2

You could create a variable called “INTERVAL” and use the function
[DaysBetween] which will give you the # of days between 2 dates. Then check
the value of INTERVAL for grouping/labeling the entries.

I have used the MonthsBetween function for a similar concept and it works
fine.

Hope this helps!

Shawn Leven
Southwestern Bell
Information Services
SL7699@SBC.COM

Sandra wrote:

I have a basic accounting table which tracks payments made and payments
due
for an account with a specific due date. There are several entries for
each
account, and I am wanting to sum the balance due/paid where the
days delinquent (today - date due) falls within several ‘buckets’. Days
delinquent is a measure that I created.

Example Results
Account 1 $25.00 10 days 1-30 30-60
60-90
Account 1 $25.00 20 days Account 1 $50.00 $25.00
$25.00
Account 1 $25.00 35 days
Account 1 $25.00 65 days

Listserv Archives (BOB member since 2002-06-25)

3

Or,

you might want to define your Days Delinquent Bucket as dimension, so that
it would return “At Due”, “30days”,“More”, etc.
Then you would do a crosstab with Account in the rows, DaysDelinquentBucket
in the columns, and Amount in the cells.

If you want to take this approach, then you need to change your Days
Delinquent measure to a dimension. From there, you can do the “Bucketing” in
the universe (decode, etc…) or in the report (if…then…else).

-Harri

Listserv Archives (BOB member since 2002-06-25)