6 Replies Latest reply: Sep 10, 2018 10:41 AM by Nagesh Setty RSS

    how to create age buckets in qliksense?

    chandana gowdar

      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

        • Re: how to create age buckets in qliksense?
          Nagesh Setty

          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

          • Re: how to create age buckets in qliksense?
            chandana gowdar

            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 ?

              • Re: how to create age buckets in qliksense?
                Nagesh Setty

                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