Dear Cognos Community,

I want to model the following in Cognos Framework Manager:

I have a table which looks (simplified) like this:

PERSON_ID MONTH AGE

1 1 19

1 2 19

1 3 20

1 4 20

1 5 20

1 6 20

1 7 20

1 8 20

1 9 20

(and no more entries for person '1').

So each row represents a person and a month and in this month, the person has an age. The age changes over time (as here).

Now, the user wants a report which answers the following question:

"Given a time period (a year, month or quarter), how many persons have which age?" For a quarter or year, the age of a person is defined as the average age within this period(rounded).

So person 1 is 19 in months 1,2 and afterwards 20. Person 1 is 19 in Quarter 1 and afterwards 20 and is 20 in the whole year.

So the user could want to create a report on a year basis. Then the expected result for our small table should be:

NUMBER OF PEOPLE 2020

age 19 0

age 20 1

Or on quarter basis:

NUMBER OF PEOPLE Q1 Q2 Q3 Q4

age 19 1 0 0 0

age 20 0 1 1 0

Or monthly:

NUMBER OF PEOPLE J F M A M J J A S O N D

age 19 1 1 0 0 0 0 0 0 0 0 0 0

age 20 0 0 1 1 1 1 1 1 1 0 0 0

The time dimension and the age dimension is already modelled. Also the number 'NUMBER OF PEOPLE' (which counts distinct PERSON_IDs). But I don't know how to model the thing with the average age. I cannot simply take the average age per person, because then I would disregard the entries where Person 1 is 19 in Q1 (as in total, the average age is 20).

I am pretty sure I cannot model this in the database (as the desired result depends on which time period the user wants to see in their report).

I would like to model this in Framework Manager -- write a filter like AGE = _round(average[Age] for [PERSON_ID], [TIME_DIMENSION]). But I don't know how to do this as this time_dimension is dynamically.

Note that "NUMBER OF PEOPLE' is used in our model in many other situations which do not involve the age. So, I would rather like to change something in the defintions of the dimensions, not in the definition of "NUMBER OF PEOPLE'.

Thanks in advance!