12 Replies Latest reply: Feb 10, 2014 9:29 AM by Mark Ritter

# Create groups based on calculation

I have a field for Date of Birth.  I want to calculate their age and show a chart which displays the number of members in each age group.

Age groups would be like 30-39, 40-49, etc.

I can calculate the age.  But no idea how to group them and build the chart.

• ###### Re: Create groups based on calculation

Create a calculated dimension that uses the fonction : class(expression, interval [ , label [ , offset ]])

Creates a classification of expressions. The bin width is determined by the number set as interval. The result is shown as a<=x<b, where a and b are the upper and lower limits of the bin. The x can be replaced by an arbitrary string stated in label. 0 is normally the default starting point of the classification. This can be changed by adding an offset.

Examples:

class( var,10 ) with var = 23 returns '20<=x<30'

class( var,5,'value' ) with var = 23 returns '20<= value <25'

class( var,10,'x',5 ) with var = 23 returns '15<=x<25'

HTH,

Michael

• ###### Re: Create groups based on calculation

You can classify your Age values in the script or in a calculated dimension, e.g. using the class() function.

=class(AgeField,5)

Then create an expression like

=count(distinct MemberField)

• ###### Re: Create groups based on calculation

Thank you.

Still not working.  The field that I used for age is returning 57 years 2 mos 5 days.  I suspect that this is not useful for what you recommended.  I think I need a field that just returns the number of years without the text.  Not sure what to use for that.

• ###### Re: Create groups based on calculation

How do you calculate the age? Could you post some sample data?

• ###### Re: Create groups based on calculation

I just used the age function against the birth_date field

So I  have this in my script:

age (birth_date) AS Age_Bracket

This returns something like 84 years 1 mon 14 days as the result.

I think that I really want something that just returns 84.  Then I can do something with that.

• ###### Re: Create groups based on calculation

The age() function should return the number of whole years. The age() function also requires two parameters, not 1 as you show in your example.

age (today(1), birth_date) AS Age_Bracket

-Rob

• ###### Re: Create groups based on calculation

This is not working in my script

• ###### Re: Create groups based on calculation

-Rob

• ###### Re: Create groups based on calculation

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='\$#,##0.00;(\$#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO PostgreSQL30 (XUserId is PTKHRaNOOLYIXUdNOLHB, XPassword is AAWJfQUOTbXATbEHfaIeFXNE);

Select

member_id,

first_name,

last_name,

member_type,

city,

zipcode,

phone,

email,

birth_date,

age (birth_date) AS Age_Bracket,

gender,

sign_up_date,

county,

firm_id,

state

from members

Where Not (Member_type = 'M')

• ###### Re: Create groups based on calculation

Ok, you are using an SQL function, here. We were talking about the QV function.

You can use the QV functions in a preceding load:

class(Age_Int, 5) as Age_Class;

age(today(1),birth_date) as Age_Int;

Select

member_id,

first_name,

last_name,

member_type,

city,

zipcode,

phone,

email,

birth_date,

age (birth_date) AS Age_Bracket,

gender,

sign_up_date,

county,

firm_id,

state

from members

Where Not (Member_type = 'M');

• ###### Re: Create groups based on calculation

Thank you.  That worked.

• ###### Re: Create groups based on calculation

Sorry for all the questions.

1.  How do I sort the axis so that I have 10-20 then 20-30 then 40-50, etc.

2.  How can I change the values on the axis to look like the above?