BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Launch Works 
Launch Works (Opens a new window)  

General Notice: No events within the next 45 days.

How to calculate age


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
friend
Principal Member
Principal Member



Joined: 03 Jul 2006

Posts: 107



PostPosted: Tue Nov 07, 2006 3:22 pm 
Post subject: How to calculate age

How to calculate age from current date and birthdate.
I tried doing months_between(currentdate,birthdate) then divide by 12, gives years but when no. of months is less than 12 it shd give the age in months soo can i use if else statement to display the sge in months and years.
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 17812
Location: Bratislava


flag
PostPosted: Tue Nov 07, 2006 3:57 pm 
Post subject: Re: How to calculate age

Hi,

since months_between is not a BO reporter function I assume that you are looking for DB-level solution. And Oracle DB. is that right?

How about the formula like this:
Code:
FLOOR ( MONTHS_BETWEEN (<date2>, <date1>) / 12) || ' year(s) and ' || ROUND ( ( MONTHS_BETWEEN (<date2>, <date1>)/ 12 - FLOOR (  MONTHS_BETWEEN (<date2>, <date1>) / 12)) * 12, 2) || ' month(s)'

_________________
BO: BI 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win and Linux servers
www.perpendulum.com

Latest Blog Posts
• 2013-07-16 Number of reports in a WebI document
• 2013-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
Back to top
friend
Principal Member
Principal Member



Joined: 03 Jul 2006

Posts: 107



PostPosted: Tue Nov 07, 2006 5:04 pm 
Post subject: Re: How to calculate age

hi

Last edited by friend on Tue Nov 07, 2006 5:49 pm, edited 1 time in total
Back to top
friend
Principal Member
Principal Member



Joined: 03 Jul 2006

Posts: 107



PostPosted: Tue Nov 07, 2006 5:09 pm 
Post subject: Re: How to calculate age

thankyou Marek Chladny
your code works but actually i dont need both years and months simultaneously
i just need age in years if the months >12 and
in months if months<12

Hope you got what iam looking for...
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 17812
Location: Bratislava


flag
PostPosted: Tue Nov 07, 2006 5:24 pm 
Post subject: Re: How to calculate age

Then you would need to use CASE...WHEN...ELSE logic. Something like:
Code:
CASE WHEN FLOOR ( MONTHS_BETWEEN (<date2>, <date1>) / 12) >= 1
          THEN ROUND ( MONTHS_BETWEEN (<date2>, <date1>) / 12, 2) || ' year(s)'
     ELSE ROUND ( MONTHS_BETWEEN (<date2>, <date1>), 2) || ' month(s)'
END

_________________
BO: BI 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win and Linux servers
www.perpendulum.com

Latest Blog Posts
• 2013-07-16 Number of reports in a WebI document
• 2013-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.1056 seconds using 17 queries. (SQL 0.0029 Parse 0.0948 Other 0.0078)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo