topic Re: Calculated Dimension - Age Buckets using Aggr? in QlikView

Calculated Dimension - Age Buckets using Aggr?

Anonymous — Wed, 20 Jul 2016 23:47:21 GMT

Hi,

I need to create a chart where I'm able to dynamically group employees into age buckets based on user input.

E.g. Table contains data similar to the following

Emp ID Age

1 29

2 35

3 48

4 34

I'd like to create age buckets 20-29, 30-39, 40-49 etc...

There will be a variable that represents the calendar year and it can be adjusted by the user via slider input.

E.g. User slides from 2016 to 2020.

I would then add the variable to the age via Age + $(variable) in order to re-calculate the age bucket distribution.

Does anybody know how I can achieve this using aggr()?

Thanks.

Thu, 21 Jul 2016 06:41:40 GMT

You can add the band in the script as follows.

load empid, age, if (age >=20 and age <=29, '20-29',

if(age >=30 and age <=39, '30-39'...

) as AgeBand

resident

<table>;

when you say your age bucket changes when using the slider do you calculate the age dynamically based on a date field? Please explain a little more.

antoniotiman — Thu, 21 Jul 2016 07:33:21 GMT

Hi Gavin,

You can use Class() function :

=Aggr(Class(Age+(vYear-Year(Today())),10,'Age'),ID)

See Attachment.

Regards,

Antonio

Anonymous — Sat, 23 Jul 2016 12:49:29 GMT

Hi Antonio,

Many thanks for the sample approach!

I also found that using a combination of Pick and If statements allows one to create buckets with tailored messages as well.