topic Re: Create groups based on calculation in QlikView

Create groups based on calculation

rittermd — Sun, 09 Feb 2014 19:42:57 GMT

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

agilos_mla — Sun, 09 Feb 2014 19:53:52 GMT

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

swuehl — Sun, 09 Feb 2014 19:56:07 GMT

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

rittermd — Sun, 09 Feb 2014 21:30:04 GMT

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

swuehl — Sun, 09 Feb 2014 22:18:26 GMT

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

Re: Create groups based on calculation

rittermd — Sun, 09 Feb 2014 23:13:59 GMT

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

rwunderlich — Mon, 10 Feb 2014 00:44:52 GMT

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

rittermd — Mon, 10 Feb 2014 01:08:04 GMT

This is not working in my script

Re: Create groups based on calculation

rwunderlich — Mon, 10 Feb 2014 03:23:14 GMT

Can you post your script, or better yet, your document log?

-Rob

Re: Create groups based on calculation

rittermd — Mon, 10 Feb 2014 13:41:30 GMT

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

swuehl — Mon, 10 Feb 2014 13:56:46 GMT

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');

Re: Create groups based on calculation

rittermd — Mon, 10 Feb 2014 14:14:45 GMT

Thank you. That worked.

Re: Create groups based on calculation

rittermd — Mon, 10 Feb 2014 14:29:15 GMT

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?