1 Reply Latest reply on Oct 18, 2018 4:44 AM by Mahfooj Khan

    Group data by relating two dates with each other

    Katharina Seehuber

      Hello,

       

      I would like to create groups by relating two dates with each other.


      More specific:

      I try to create age groups (0-20, 21-40 and so on), based on birth date of course.

      The second date factor is an emigration date (by year, 1933, 1934 until 1943).

      Someone who is born in 1918 and emigrates in 1933 belongs to age group 0-20, but if he would emigrate in 1943 he would be part of age group 21-40.

       

      How do I generate flexible age groups according to 'emigration date related to birth date'?

       

      Thanks!

        • 1. Re: Group data by relating two dates with each other
          Mahfooj Khan

          Hi,

           

          I've assumed you've data set like this

          Using MAKEDATE() I'll convert Emigration Year field to date field.

           

          DOE: MAKEDATE([Emigration Year],1,1)

          Now, will take difference of year from both DOB and DOE and then we'll create a bin from that

           

          Year Difference: DATEDIFF('year',DATETRUNC('year',[DOB]),[DOE])

           

          Create bin from the meaures (Year Difference) set the bin size 20

          Now drag the created bins in columns shelf and drag person field in rows and take the distinct count

          workbook attached for your reference, let us know if this help else share your sample data in packaged workbook.

           

          Mahfooj

          1 of 1 people found this helpful