how to create age buckets in qliksense?

[Anonymous]

Hi all ,

i am fetching data from SQL database which consists of the column "Date of Birth" in the data.

now in qliksense in have created a calculated dimension to calculate age using the below given expression:

Age(Today(),DateofBirth)

this works fine. but the user wants the buckets for the ages calculated , like mentioned below

buckets :

18-30 years

31 - 45 years

45+ years

now how do i do this ??

i refered to several posts similar to this scenrio but i am not able to ficgure out the right way. can any one please help me out.

as i am new to qliksense i really need your help guys.

also note : age is not calulated in the script . it is calculated in the calculated dimension.

Thanks

[nsetty]

Can you check...

If(

Age(Today(),DATE((Timestamp(Timestamp#([Patient DOB], 'M/D/YYYY h:mm') ))) ) < 19, Dual('[, 19)', 1)

,If(Age(Today(),DATE((Timestamp(Timestamp#([Patient DOB], 'M/D/YYYY h:mm') ))) ) >= 19 and Age(Today(),DATE((Timestamp(Timestamp#([Patient DOB], 'M/D/YYYY h:mm') ))) ) < 31, Dual('[19, 31)', 2)

,If(Age(Today(),DATE((Timestamp(Timestamp#([Patient DOB], 'M/D/YYYY h:mm') ))) ) >= 31 and Age(Today(),DATE((Timestamp(Timestamp#([Patient DOB], 'M/D/YYYY h:mm') ))) ) < 45, Dual('[31, 45)', 3)

,If(Age(Today(),DATE((Timestamp(Timestamp#([Patient DOB], 'M/D/YYYY h:mm') ))) ) >= 45, Dual('[45, )', 4))))

) AS [Age As Of Now (Bucketed)]

Editing a table with data profiling cards - Qlik Sense - YouTube

[Anonymous]

Hi Nagesh ,

Should this be created as a dimension ??

and then use the dimension in the chart ??

i tried doing it ,  it said there is an error in the syntax ..

did that work for u  ?

can u share an image of the logic implemented ?

thanks for replying

[nsetty]

Hi Chandana,

Try using

=NUM( [Age As Of Now (Bucketed)])

for dimension... It worked for me in filter pane.

[nsetty]

Below steps i followed in Data Manager;

1) Create a calculated field "DOB Age", using Age(Today(1), [Patient DOB])

2) Create buckets

[Anonymous]

Thank u for the screen shots ,

with reference to those i am trying to create a calculated column in data manager tab. but i cannot see the table with data here , it is asking for synchronization of data .

and when i selected synchronizing data it is taking very long time to load the tables,

i am not getting where is the problem.

i am loading data through query from sql server.

can u pls suggest on what is wrong here and how to handle it ?

[nsetty]

Hi Chandana,

After creating buckets using front end, it adds below field/line a script. 

If(

Age(Today(),DATE((Timestamp(Timestamp#([Patient DOB], 'M/D/YYYY h:mm') ))) ) < 19, Dual('[, 19)', 1)

,If(Age(Today(),DATE((Timestamp(Timestamp#([Patient DOB], 'M/D/YYYY h:mm') ))) ) >= 19 and Age(Today(),DATE((Timestamp(Timestamp#([Patient DOB], 'M/D/YYYY h:mm') ))) ) < 31, Dual('[19, 31)', 2)

,If(Age(Today(),DATE((Timestamp(Timestamp#([Patient DOB], 'M/D/YYYY h:mm') ))) ) >= 31 and Age(Today(),DATE((Timestamp(Timestamp#([Patient DOB], 'M/D/YYYY h:mm') ))) ) < 45, Dual('[31, 45)', 3)

,If(Age(Today(),DATE((Timestamp(Timestamp#([Patient DOB], 'M/D/YYYY h:mm') ))) ) >= 45, Dual('[45, )', 4))))

) AS [Age As Of Now (Bucketed)]

You can modify to suit your requirement and use it.

Might be your data set is huge hence taking time to load

Nagesh