3 Replies Latest reply on Aug 26, 2016 12:18 PM by Michael Franz

    Retention - current year / prior year in a table

    Michael Franz

      I have a table that has Employee, Year, Renewal, Total and Retention. The difficulty I am having is dividing the current year RENEWAL / prior year TOTAL... See the attached excel file. Probably pretty simple. I'm new and just missing something....

       

      Michael

        • 1. Re: Retention - current year / prior year in a table
          Stephen Rizzo

          It's actually not as simple as it sounds. Because you are referencing previous values from the table in your calculation, that field is being computed after most of your filters are run. The trick here will be to filter out previous years in a step that occurs at the same time or after your calculation. Assuming that your are calculating retention using the LOOKUP function (making the field a table calculation), I would suggest filtering out the previous years using a table calculation as well, either using INDEX() or FIRST().

           

          To be more specific, try the following:

           

          1. Remove whatever year filter you currently have
          2. Create a calculated field called FIRST with the following formula: FIRST()
          3. Add FIRST to your filters, allowing values from 0-2 only

           

          You may also need to adjust the "Compute Using" setting for FIRST to match what you have for your retention calculation.

           

          I hope that helps!

          • 2. Re: Retention - current year / prior year in a table
            chris.moore.11

            You could try to use the table calc "lookup" to bring back the previous year.

             

            See the attached. "Previous Year Total" is a table calc that brings back the previous year. "Retention" calculates the percentage. How the table calcs are computed (order, direction) are important and dependent on the way you have it set up.

             

            (Note: you dont actually even need the first calculated field you can embed it straight into the retention calculation)

             

            Hopefully this helps.

            1 of 1 people found this helpful
            • 3. Re: Retention - current year / prior year in a table
              Michael Franz

              Thank you.... Got what I needed....