6 Replies Latest reply on Oct 17, 2018 10:36 AM by Ruby Delgado

    Calculated Field for Score Change

    Ruby Delgado

      Hello,

       

      I  am working on a chart that shows a students change in score from prior year. However, I am struggling with how I would write this out in a Tableau Calculation.

      Data is as follows:

       

      StuId:      School Year     Score

      123456     2016               750

      123456     2017               780

       

      Want to see Growth: Score(2017) - Score(2016)

       

      I'd like to create the following:

       

        • 1. Re: Calculated Field for Score Change
          Hari Ankem

          You can create a calculated field for the Growth as shown below:

           

          {FIXED [StuId]: SUM(IF [School Year] = 2017 THEN [Score] END)}

          -

          {FIXED [StuId]: SUM(IF [School Year] = 2016 THEN [Score] END)}

           

          Note: If in-case you have any dimensions in the filter, you may need to add to context to get the correct growth.

          • 2. Re: Calculated Field for Score Change
            Ruby Delgado

            It seems it would work but I'll need to figure out where I'm going wrong

             

            StuID     SchoolYear     Score     Subject

            123456     2016               750          ELA06

            123456     2017               785          ELA07

             

            These scores are attached to a roster list and therefore are duplicated for every course

            • 3. Re: Calculated Field for Score Change
              Hari Ankem

              If the scores exist for a student for every subject for 2016 and 2017, then you may need to include the subject too in the fixed dimensions.

               

              {FIXED [StuId],[Subject]: SUM(IF [School Year] = 2017 THEN [Score] END)}

              -

              {FIXED [StuId],[Subject]: SUM(IF [School Year] = 2016 THEN [Score] END)}

               

              I am not sure what you are really trying to achieve. So, this may be more of guessing from my side currently. If this is not the solution you need, you may want to attach your packaged workbook (twbx) with data extracted in it and specify the exact output you need from that.

              • 4. Re: Calculated Field for Score Change
                Ruby Delgado

                Hari,

                 

                I have put together similar but anonymous data.

                I have students in a teachers class and teacher needs to see how they performed year after year as shown in second sheet. I'd like to display this as shown below.

                Your assistance is much appreciated.

                • 5. Re: Calculated Field for Score Change
                  Hari Ankem

                  Does this look right?

                  1.png

                   

                  To validate the numbers, I used this:

                  1.png

                   

                  The workbook is attached. Hope this helps.

                  • 6. Re: Calculated Field for Score Change
                    Ruby Delgado

                    Not quite. Only want to show current Roster Year:

                    Example: For Teacher Roster 2017-2018

                    Only show (top row) assessment score by student for 2017-2018

                    Bottom row - show difference from prior year (2017-2018 - 2016-2017)

                    I created a calc with the following

                     

                    SUM(IF [Testing School Year] = '1617' THEN [Test Scale Score] END)

                    -

                    SUM(IF [Testing School Year] = '1516' THEN [Test Scale Score] END)

                     

                    But now I need AVG Growth for that classroom:

                    I tried the following but didn't work

                     

                    AVG((SUM(IF [Testing School Year] = '1617' THEN [Test Scale Score] END)

                    -

                    SUM(IF [Testing School Year] = '1516' THEN [Test Scale Score] END))