7 Replies Latest reply on Oct 22, 2018 7:54 AM by Hari Ankem

# Calculated Field for Score Change

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

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

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

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

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.

• ###### 5. Re: Calculated Field for Score Change

Does this look right?

To validate the numbers, I used this:

The workbook is attached. Hope this helps.

• ###### 6. Re: Calculated Field for Score Change

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))

• ###### 7. Re: Calculated Field for Score Change

You need to use WINDOW_AVG instead of AVG.