Aging periods

[Nik]

Hi,

I need to create a crosstab report that has in columns the age of debt
(maturity date compared to current date) and in the rows the amount of debt.

As a measure I need to specify the number of debt by amount and by age (period).


                         | 0-3 month | 4-6 month | 7-9 month | 10-12 month
------------------|-----------|-----------|-----------|-------------
$0-$1000            |     5         |     4         |    7          |     11
$1001-$10,000     |     9         |     14       |    4          |     7
10,001-$100,000  |     3         |     9         |    17         |     6

In the mart I use there is a table that has the debt amount together with a surrogate
key to the date dimension, so I can get the date of the debt record. My main problem
is with creating the aging period in ReportStudio.
When I specify a filter like [age] between 0 and 90 days, ReportStudio handles it like a condition and
returns true or false (1 or 0) depending on the date of the record.
(I am able to calculate the date by calculating the days between today (current date)
and the debt record's date, which I referred to as [age] above).

Any tips on how to do this in ReportStudio? I am using 8.3.

Much appreciated,
Nik

[blom0344]

Code Select Expand


CASE WHEN
[Age] BETWEEN 0 AND 90
THEN ('0 - 3 MONTH')
WHEN
[Age] BETWEEN 91 AND 180
THEN ('3 - 6 MONTH')
...............
...............
END


Depending on your needs you can also use special date calc functions that Cognos provides, but the trick is to create 'buckets' by using CASE constructs and refrain from using filters..