How to create a Age Grouping Variable
1 December, 2009
Please, I�d like your suggestion how to get the Age Group of the employees (0-20, 20-30, 30-40, 40-50, 50-60, 60+). The customer want to use it as a filter in the Analytic Dashboard. The database has the field birthdate.
Where is the best place to do it (Virtual Table, Calculated Fields, Special Function or Group Columns?) I need answer it to a prospect. I know that it�s not a Yellowfin software problem, but I appreciate your help.
Where is the best place to do it (Virtual Table, Calculated Fields, Special Function or Group Columns?) I need answer it to a prospect. I know that it�s not a Yellowfin software problem, but I appreciate your help.
Yes there are many ways to do this. There isn't really a "best" way to do this.
The main problem here is calculating Age from DOB.
You can use a hard table in their database, based on Age:.
Age Group
1 0-20
2 0-20
3 0-20
4 0-20
.. ..
21 20-30
22 20-30
You may have to add age to their source table or use a calculated field to calculate age in a virtual table.
You could use DOB, but you'd need a job to update this nightly., as the groups will obviously scroll by 1 each day.
DOB Group
1/1/2001 0-20
2/1/2001 0-20
3/1/2001 0-20
4/1/2001 0-20
.. ..
1/1/1985 20-30
2/1/1985 20-30
Use a virtual table with something like this: (This pseudo code)
CASE WHEN YEAR(SYSDATE) - YEAR(DOB) < 20 THEN "0-20"
WHEN YEAR(SYSDATE) - YEAR(DOB) < 30 THEN "20-30"
WHEN YEAR(SYSDATE) - YEAR(DOB) < 40 THEN "30-40"
WHEN YEAR(SYSDATE) - YEAR(DOB) < 50 THEN "40-50"
END
This problem varies with difficulty based on the functions available in the source database system.
Hope this has helped a little.
The main problem here is calculating Age from DOB.
You can use a hard table in their database, based on Age:.
Age Group
1 0-20
2 0-20
3 0-20
4 0-20
.. ..
21 20-30
22 20-30
You may have to add age to their source table or use a calculated field to calculate age in a virtual table.
You could use DOB, but you'd need a job to update this nightly., as the groups will obviously scroll by 1 each day.
DOB Group
1/1/2001 0-20
2/1/2001 0-20
3/1/2001 0-20
4/1/2001 0-20
.. ..
1/1/1985 20-30
2/1/1985 20-30
Use a virtual table with something like this: (This pseudo code)
CASE WHEN YEAR(SYSDATE) - YEAR(DOB) < 20 THEN "0-20"
WHEN YEAR(SYSDATE) - YEAR(DOB) < 30 THEN "20-30"
WHEN YEAR(SYSDATE) - YEAR(DOB) < 40 THEN "30-40"
WHEN YEAR(SYSDATE) - YEAR(DOB) < 50 THEN "40-50"
END
This problem varies with difficulty based on the functions available in the source database system.
Hope this has helped a little.