function years-between in Impromptu v6 & v7

[vikFinnegan]

Hi,

We've run into the following problem after migrating from Impromptu v6.1 to v7.0.

The calculation of the function 'years-between' differs in v6.1 from v7.0. When used in v6.1 it can be used to correctly calculate 'age' at a given date; in v7.0 the age calculation doesn't run correctly. Is this 'by design' or isn't it -- and if it isn't, might there already be a fix in v7.1 or v7.2?

Here's an example to clarify the difference:
We do the administration of students at all schools in our city. The students' ages range between 4 and 23. In our Oracle db the students' birth dates are stored in a datetime field. School years run from August until July -- i.e. the school year starts at the first of August.

We need to calculate ages from students at the start of year -- e.g.

years-between(17/04/1988;01/08/2002)

should result in age 4. Both v6.1 and v7.0 correctly return this result. However, all birthdates later then the first of August will result in different ages in v6.1 and v7.0. For example,

years-between(17/09/1988;01/08/2002)

should result in age 3. Impromptu v6.1 returns the correct result: age 3; Impromptu v7.0 however returns the -- wrong -- result: age 4.

Is this new behavior of the function years-between in v7.0 'by design' or isn't it -- and if it isn't, might there already be a fix in v7.1 or v7.2?

I hope you could help us out. Thanks in advance.

Vik

 

[canhe]

Hello Vik,


Why not use the database functions "Year" and "Month" to get around this. Not only will it work but it would be faster as both functions are database functions. I use informix so I would assume they are in oracle as well.
Year (01/08/2002) -> return 2002
Year (17/04/1988) -> return 1998

2002 - 1998= 4 * 12 = 48 months +

month (01/08/2002) -> return 08
month (17/04/1988) -> return 04

08 - 04 = 4 months

Add the months together
4 + 48 = 52
divide by 12 to get the years -> 4.3 years

 

[BruceReed]

Vik

I have only worked on ver 6.6, but I am guessing Cognos is rounding years. Your 2nd example student is 3yrs, 11 months (3.91) old. In some versions, the number is rounded, in others it is truncated. Canhe's solution seems like the best work-around.

However in Canhe's solution you will have pay attention to negitive months when required. In the 2nd students example, his solution would be:

year(01/08/2002)-year(17/9/1998)=4 year

month(01/08/2002)-month(17/09/1998)= 8-9 =-1 month (note the negative sign)

adding together to get the correct 47 month-old student.

Cheers
Bruce

 

[vikFinnegan]

Canhe, Bruce,

Thanks for your helpful tips. We've just run a test -- and yes, it works!!

Thanks again

Vik