Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
LOAD *,
class(Age_Int, 5) as Age_Class;
LOAD *,
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');
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
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)
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.
How do you calculate the age? Could you post some sample data?
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.
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
This is not working in my script
Can you post your script, or better yet, your document log?
-Rob
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')