8 Replies Latest reply on Apr 5, 2018 11:22 AM by Jim Dehner

    Calculating Retirement Benefits using service years and ages

    Kelly Martin

      I am currently creating a worksheet to calculate retirement benefits for employees based on their ages and their years of service.

       

      Ages are currently in the dataset as real values (e.g., 65.00)

       

      Years of Service (called, [Adjusted Service Date]) are currently using the date format 'yyyy/MM/DD'

       

      These are the parameters that determine Full or Reduced benefits:

       

      Full Benefits:

      1. Aged 65 years or more and years of service between 5 years ago and 29 years and 11 months ago

      2. Ages between 60 and 64.99 years and years of service between 25 years ago and 29 years and 11 months ago

      3. Any age with years of service beginning more than 30 years ago

       

      Reduced Benefits:

      1. Ages between 50 and 59.99 and years of service between 20 years ago and 29 years and 11 months ago

      2. Ages between 60 and 64.99 and years of service between 5 years ago and 24 years and 11 months ago

       

      Ideally, this calculated field will act dynamically, adjusting what 5 years ago, 20 years ago, etc. is based on the current date.

       

      My first instinct was to create calculated fields for each date range using the DATEADD function.

       

      For example: a calculated field titled, '5yrs ago' with the formula: DATEADD ('year', -5, [Adjusted Service Date])

      This calculated field displays the years and number of records for years of service beginning in 2013 and going back in time to the earliest date.

      I created calculated fields for '30yrs ago', '25yrs ago' and '20yrs ago' as well, and these fields act in a similar manner.

       

      The full formula I used to calculate retirement benefits is as follows:

       

      IF  [AGE]  >=  65  AND  ( [Adjusted Service Date]  >=  [5yrs ago]  OR  [Adjusted Service Date]  <  [30yrs ago] )

      OR  ( [AGE]  >=  60  AND  [AGE]  <  65 )  AND  ( [Adjusted Service Date]  <  [30yrs ago]  OR  [Adjusted Service Date]  >=  [25yrs ago])

      OR  [Adjusted Service Date]  <=  [30yrs ago]

      THEN  'Full Benefits'

      ELSEIF  ( [AGE]  >=  50  AND  [AGE]  <  60 )  AND  ( [Adjusted Service Date]  <=  [20yrs ago]  AND  [Adjusted Service Date]  >  [30yrs ago] )

      OR  ( [AGE]  >=  60  AND  [AGE]  <  65 )  AND  ( [Adjusted Service Date]  >=  [5yrs ago]  AND  [Adjusted Service Date]  <=  [25yrs ago] )

      THEN  'Reduced Benefits'

      END

       

      I am receiving values from this formula but the numbers are not quite right.  This formula is currently over-reporting my full benefits by 310 individuals and under-reporting my reduced benefits by 65.

       

      I would greatly appreciate some guidance from the Tableau world in how to tackle this issue.

       

      Thank you

        • 1. Re: Calculating Retirement Benefits using service years and ages
          Naveen Ambati

          Hi,

           

          Can you please attach the workbook with sample data?

           

          Thank you

          • 2. Re: Calculating Retirement Benefits using service years and ages
            Jim Dehner

            Hi Kelly

            I too would like to see the twbx workbook with your data included -

            that said I think the issue is in the Logic  in your if statement I think the 2 BOLD OR  below should be AND

             

             

             

            IF  [AGE]  >=  65  AND  ( [Adjusted Service Date]  >=  [5yrs ago]  OR  [Adjusted Service Date]  <  [30yrs ago] )

            OR  ( [AGE]  >=  60  AND  [AGE]  <  65 )  AND  ( [Adjusted Service Date]  <  [30yrs ago]  OR  [Adjusted Service Date]  >=  [25yrs ago])

            OR  [Adjusted Service Date]  <=  [30yrs ago]

            THEN  'Full Benefits'

            ELSEIF  ( [AGE]  >=  50  AND  [AGE]  <  60 )  AND  ( [Adjusted Service Date]  <=  [20yrs ago]  AND  [Adjusted Service Date]  >  [30yrs ago] )

            OR  ( [AGE]  >=  60  AND  [AGE]  <  65 )  AND  ( [Adjusted Service Date]  >=  [5yrs ago]  AND  [Adjusted Service Date]  <=  [25yrs ago] )

            THEN  'Reduced Benefits'

            END

             

            Like this

             

             

            IF  [AGE]  >=  65  AND  ( [Adjusted Service Date]  >=  [5yrs ago]  AND  [Adjusted Service Date]  <  [30yrs ago] )

            OR  ( [AGE]  >=  60  AND  [AGE]  <  65 )  AND  ( [Adjusted Service Date]  <  [30yrs ago]  AND  [Adjusted Service Date]  >=  [25yrs ago])

            OR  [Adjusted Service Date]  <=  [30yrs ago]

            THEN  'Full Benefits'

            ELSEIF  ( [AGE]  >=  50  AND  [AGE]  <  60 )  AND  ( [Adjusted Service Date]  <=  [20yrs ago]  AND  [Adjusted Service Date]  >  [30yrs ago] )

            OR  ( [AGE]  >=  60  AND  [AGE]  <  65 )  AND  ( [Adjusted Service Date]  >=  [5yrs ago]  AND  [Adjusted Service Date]  <=  [25yrs ago] )

            THEN  'Reduced Benefits'

            END

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            • 3. Re: Calculating Retirement Benefits using service years and ages
              Kelly Martin

              Apologies for the delay, but a few other items creeped in during the day that prevented me from providing sample data and a .twbx.

               

              In this sample .twbx I have attached anonymized data.  The fields from the original data source that are pertinent to calculating retirement benefits include:

              1. [AGE]

              2. [Adjusted Service Date]

               

              This sample data also includes calculated field values not in the original data source and are used to provide different approaches to calculating retirement benefits.

               

              This workbook includes the original method used in the first post and is reflected in the workbook sheet titled 'DATEADD'

              This sheets utilizes the calculated variables [5yrs ago], [20yrs ago], [25yrs ago] and [30yrs ago], which include the <DATEADD> function.

              These <DATEADD> fields are then compiled in the calculated field [DATEADD Retirement Benefits].

               

              A second approach was also used in the workbook sheet titled 'DATEDIFF'

              This approach utilized the <DATEDIFF> function to determine the difference between the date <TODAY()> and the dates in the [Adjusted Service Date] field.

              This is first calculated in the calculated field [DATEDIFF Years Ago] where I include the calculation:

               

                  DATEDIFF('year', TODAY(), [Adjusted Service Date])

               

              This equation calculates the difference between the current date and the different years reflected in the [Adjusted Service Date] field.

              [DATEDIFF Years Ago] is then compiled in the calculated field [DATEDIFF Retirement Benefits], which is the main field used for this worksheet.

              The formula includes:

               

                  IF [AGE] >= 65 AND ([DATEDIFF Years Ago] >= -5 AND [DATEDIFF Years Ago] < -30) 

                  OR ([AGE] >= 60 AND [AGE] < 65) AND ([DATEDIFF Years Ago] < -30 AND [DATEDIFF Years Ago] >= -25)

                  OR [DATEDIFF Years Ago] <= -30

                  THEN 'Full Benefits'

                  ELSEIF ([AGE] >= 50 AND [AGE] < 60) AND ([DATEDIFF Years Ago] <= -20 AND [DATEDIFF Years Ago] > -30)

                  OR ([AGE] >= 60 AND [AGE] < 65) AND ([DATEDIFF Years Ago] >= -5 AND [DATEDIFF Years Ago] <= -25)

                  THEN 'Reduced Benefits'

                  END

               

              I further demonstrate this calculation in the workbook sheet 'DATEDIFF Years Ago' which includes a simple table showing the [Number of Records], the [Adjusted Service Date] years, and the difference in years as calculated by [DATEDIFF Years Ago].

              So, for example, for the year 2013, the [DATEDIFF Years Ago] will show -5, for 1998 it will show -20, etc.

               

              Finally, to further brake down the process of calculating retirement benefits, I have broken each portion of the calculation down into separate workbook sheets, titled, 'Full 1', 'Full 2', 'Full 3', 'Reduced 1', 'Reduced 2.'

              These worksheets are then compiled into two separate dashboards, one for all the full benefit parameters, titled, 'All Full Benefits,' and one for the reduced benefit parameters, titled, 'All Reduced Benefits.'

              These worksheets are composed of simple filters, reflecting the [AGE] and [Adjusted Service Date] ranges used for each portion of the retirement benefits calculation.

              For example, 'Full 1' filters all records with an [AGE] that is >=  65, and [Adjusted Service Date] between 3/31/1998 (approximately 29 years and 11 months ago) and 4/1/2013 (approximately 5 years ago).

              'Full 2' filters all records with an [AGE] that is >= 60 and <= 64, and [Adjusted Service Date] between 3/31/1998 (approximately 29 years and 11 months ago) and 4/1/1993 (approximately 25 years ago).

               

              All three of these methods provide me different numbers.

               

              Also, notice that [Employee Type] is used as a grouping field for each worksheet, as the final output should provide the viewer with a breakdown of retirement benefits for each employee type.

               

              Lastly, I am basing this workbook off of a previous calculation used for an excel output, but that I not have access to view. 

               

              I have tried to be as thorough as possible in explaining my methods and modes of thinking, if I need to clarify anything please do not hesitate to ask.

               

              Thank you.

              • 4. Re: Calculating Retirement Benefits using service years and ages
                Jim Dehner

                Hi Kelly - I am looking at your book and I am confused on the calculation for the years of service

                 

                There is a date field called Adjusted Service Date -

                 

                you use it to determine the Diff year ago as           DATEDIFF('year', TODAY(), [Adjusted Service Date])  which gives the difference in years between the adj service date and today

                 

                you then use that difference in the long formula for date diff retirement  as the Years of Service  -     so here it looks like adjusted service date is similar to a Hire Date maybe adjusted for something

                 

                 

                then you use it in the date add version of the formula to compare the Adjusted Service Date to N-years ago - but it I look at the actual data the N-years ago dates are taken from the Adjusted service date - effectively moving the date back in time

                 

                so the questions are -

                     are all employees active - i.e. not retired? (don't see a relation to a retirement date)

                     is the Adjusted Service Date - a Hire Date equivalent?

                 

                Jim

                • 5. Re: Calculating Retirement Benefits using service years and ages
                  Kelly Martin

                  Hey Jim,

                   

                  Thank you for your reply.  Yes, [Adjusted Service Date] is equivalent to a hire date, and it is adjusted to something I'm unsure of, most likely setting the day of the month to the 1st, so [Adjusted Service Date] of 2013/04/01 instead of 2013/04/15.

                   

                  All employees are currently active and not retired, so the point is to determine retirement plan eligibility based on employee age and how long they have been employed.

                   

                  So, for example, if I need to know who has been employed for a period of time between 5 years ago (2013) and 30 years ago (1988), I can essentially use DATEDIFF('year', TODAY(), [Adjusted Service Date]) to calculate the years between 2013 (5 years ago) and 1988 (30 years ago).

                   

                  Saying, "IF [DATEDIFF Years Ago] <= -5 AND [DATEDIFF Years Ago] > -30" should ideally give me a range between 1988 and 2013.

                   

                  Thanks,

                  Kelly

                  • 6. Re: Calculating Retirement Benefits using service years and ages
                    Jim Dehner

                    Ok

                    then what determines the retirement status is the combination of 2 values =

                              age which is a scalar (fixed) for each employee

                              The difference in years between today and their adjusted date (hire date)

                     

                    see the attached  

                     

                    the years of service is this

                     

                    I believe that the coding for full or reduced is this  - at least the best I can determine

                     

                     

                    I just made a text chart of the results - I like to look at number

                     

                     

                     

                    In the attached I copied your data and added an empid - just a sequential number a1-an -  it makes it easier to work with the data

                     

                    Jim

                     

                    If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                    • 7. Re: Calculating Retirement Benefits using service years and ages
                      Kelly Martin

                      That looks nice and solves my problem.

                       

                      Thank you Jim!

                      1 of 1 people found this helpful