1 Reply Latest reply on Sep 2, 2016 12:12 PM by Kyle Boyce

    Calculation using Prior and Following Year to calculate replacement

    Sherzodbek Ibragimov

      Hi Guys,

       

      I have an issue of calculating replacement which is calculated by:

       

      [Total Student Headcount (Year 1) - Graduated Students (Year 1)] - [Total Student Headcount (Year 2)-New Students (Year 2)]. I can't think of anything how I can attribute current and prior year info into calculated field. Could you please guide me how I can calculate this formula or any guidance how to approach this calculation. Thank you so much and I really appreciate that.

       

       

      - Total Student Headcount is a union of data sources that have new and returning (could be also graduating) students for each year

      - New Students is a union of data sources that have only new enrolled students for each year

      - Graduated Students is a union of data sources that have only graduated students for each year

      Main relationship attribute between all of those data is ID (at least).

        • 1. Re: Calculation using Prior and Following Year to calculate replacement
          Kyle Boyce

          You can create multiple calculations using a DATEPART() on your fields. For example,

           

          Create Calculated Field called: "2016 Student Headcount"

           

          IF DATEPART('year',[field]) = DATEPART('year',TODAY())

          THEN SUM([Headcount])

          END

           

          Create another Calculated Field called: "2016 Graduating Headcount"

           

          IF DATEPART('year',[field]) = DATEPART('year',TODAY())

          AND [Student Status] = "Graduating"

          THEN SUM([Headcount])

          END

           

          Do this for the specific calculations you need, then use the calculated fields in another equation. For example,

           

          [2016 Student Headcount] - [2016 Graduating Headcount]

           

          I'm sure you get the point...