Creating 5 year group

[djieon]

Hi All,

I have a field called RM_COMMON_ATTRIBUTES.START_DATE

this is a date field.

I am looking to group on this field but want to group in 5 year blocks (I.E. - 0-5 years, 5-10 years, 10-15 years, 15-20 years etc...)

Is there an easy way to do this or do i need to write lots of formulas to make this happen?

thanks in advance

David.

 

[IanWaterman]

You only need 1 formula

If RM_COMMON_ATTRIBUTES.START_DATE >= dateadd("yyyy", -5, currentdate) then ' 0-5 Years'
else
If RM_COMMON_ATTRIBUTES.START_DATE >= dateadd("yyyy", -10, currentdate)
and RM_COMMON_ATTRIBUTES.START_DATE < dateadd("yyyy", -5, currentdate) then ' 5-10 Years'
else
If RM_COMMON_ATTRIBUTES.START_DATE >= dateadd("yyyy", -15, currentdate)
and RM_COMMON_ATTRIBUTES.START_DATE < dateadd("yyyy", -10, currentdate) then '10-15 Years'
etc

Group on this formula.

Ian

 

[lbass]

Or you could use a formula like these to group on:

//group based on last year in 5-yr sequence (e.g., 2010 would include 2006 - 2010):
-int(-year({RM_COMMON_ATTRIBUTES.START_DATE})/5)*5

//group based on first year in 5-yr sequence (e.g., 2005 would include 2005 - 2009):
int(year({RM_COMMON_ATTRIBUTES.START_DATE})/5)*5

-LB