1 Reply Latest reply: Oct 15, 2018 1:12 PM by Simon Hogg RSS

    create a year groups

    xxxxx yyyyy

      I have a Experience field in a table. I have to create a year wise experiance

       

      Ex:         Experiance

                2 year 1 months 10 days

                1 year 7 months 15 days

                2 year 1 months 10 days

                2 year 1 months 10 days

               4 year 1 months 10 days

               1 year 1 months 10 days

               4 year 1 months 10 days

               2 year 1 months 10 days

               5 year 1 months 10 days

               3 year 1 months 10 days

               3 year 1 months 10 days

               4 year 1 months 10 days

               3 year 1 months 10 days



      i want create group like exp 0-1 year

                                                   1 -2 year

                                                    3-4 year

                                                     4-5 year

                                                     >5 Years

      Can you please suggest me..

        • Re: create a year groups
          Simon Hogg

          Look up the CLASS function.

           

          =Class(experience, 1 , 'Experience')

           

          Will give;

              0 <= Experience < 1

              1 <= Experience < 2

              Etc.


          as data labels.


          If you want to put limits (>5 Years in your example) then you can wrap it in an IF statement;


          IF ([Experience] > 5 , '>5 Years' , Class(experience, 1 , 'Experience'))


          but in that case I'd be tempted to do this with IF and DUAL in the load script;


          LOAD

          If ([Experience] > 5 , DUAL('>5 Years' , 1) ,

               If ([Experience] > 4 , DUAL ('4-5 year' , 2) ,

               If ([Experience] > 3 , DUAL ('3-4 year' , 3) ,

               If ([Experience] > 2, DUAL ('2-3 year' , 4) ,

               If ([Experience] > 1 , DUAL ('1-2 year' , 5) ,

               If ([Experience] > 0 , DUAL ('0-1 year' , 6) )))))) AS [Experience];