Talis Decisions
Submitted by Brian Crampton on Fri, 2006-07-28 10:30.
Welcome to the Talis Decisions forum on the TDN.
Have you got questions about the universes, suggestions for new universes or objects or useful tips on creating reports within Talis Decisions?
With the refresh of the technology underpinning Talis Decisions the opportunities for discussing and sharing report templates is greatly increased as the possibilities of Talis Decisions have increased.
The postings from the existing forum will be moved to this forum.
Age Profiles
I'm a bit confused! (Which is quite normal)
I am trying to do some age profiling for one of our managers, but I don't understand what's happening with the 'Borrowers Age (in years)' field.
If I do the following report:
Home Site, Borrower Type, Barcode, Borrower name, Date of Birth - with the condition being 'Active/Deleted = Active'. This results in 117019 rows being returned.
However, If I do the same query, but include 'Borrowers Age (in years)', I get 78203 rows returned.
I know 'Borrowers Age (in years)' only includes those records where there is a DOB, so if I do another query showing the records with no DOB (i.e. DOB is null) then I get 1323 rows.
So... What's puzzling is: If I have 117019 records in total, 78203 with ages and 1323 with no dob, where are the missing 37493 records?
Any ideas anyone?
http://www.wirral-libraries.net/
http://www.stembystem.co.uk/
Figure it!
What was confusing me was that some of the Date of Births where 'null' and the missing 37493 ones were in as '1970/01/01' which I guess are some sort of default?
http://www.wirral-libraries.net/
http://www.stembystem.co.uk/
Age in years
The actual SQL is that is generated by the Borrower age (in years) object
SELECTdatediff(YY,dbo_BORR1.DATE_OF_BIRTH,getdate())
FROM
dbo.BORROWER dbo_BORR1
WHERE
( dbo_BORR1.DATE_OF_BIRTH <> '01 Jan 1970 12:00AM' )
as you can see borrowers with a DATE_OF_BIRTH value of '01 Jan 1970 12:00AM' are not included. That datetime is a default value and means that nothing is displayed on-line. If you do have someone who was born on Jan 1 1970 you actually have to set the time to something other than 12:00AM. This has to be done via the database as Alto does not allow you to set a time for the date of birth.
This is a known issue with Text and Alto and I think there is an FAQ. It is not an issue as such with Decisions but the data itself.
I am guessing that the vast majority of people with the DATE_OF_BIRTH of '01 Jan 1970 12:00AM' have no date of birth set rather than being born on that day.
I'm not sure about those with the date of birth that is NULL. I'm not aware of why that could happen and that might need further investigation. I would assume that if the borrower has a date of birth of NULL that the date of birth is unknown and that it should be '01 Jan 1970 12:00AM'. It would be possible to change the NULL values to '01 Jan 1970 12:00AM'.
A wider question is whether you would expect to see borrowers that don't have a date of birth at all and what you would expect to see for borrowers that don't have a data of birth. Given the nature of the data and the database shape to list all borrowers with a dob if present you would have to do two queries, all borrowers and borrowers with ages and then join them together on the borrower barcode.
Brian Crampton
Developer, Talis
Negative Ages
Probably not relevant, but Talis allows dates of up to 2038 in the dob field. So, could the result of the Age calculation be negative?
negative ages
It is possible as Alto does not prevent dates in the future being entered in the Date of Birth field.
Hopefully not a common problem the extent of which can be checked using Talis Decisions
Brian Crampton
Developer, Talis