3 Replies Latest reply on Oct 12, 2018 1:47 PM by Ruby Delgado

# Calculating test score gains

Hi all,

I have student test score data with date tested and score. Students test on an ongoing basis throughout the year, so the dates of the initial test and follow up test varies by student, with some students taking multiple follow up tests on different dates. I want to create a calculated field that show the difference between the score of the most recent test date and the previous test date.

I imagine this could be done using a table calculation as well, but if possible, I'd prefer to do it using a calculated field. That way I could use that field (test gain) as a measure or filter to combine with other data points. This doesn't seem like it should be that hard, but I'm stuck on how to go about it. Prior to getting Tableau, we'd reshaped the data in SPSS and used some long syntax to calculate this, but I'm not able to translate.

- Mike

• ###### 1. Re: Calculating test score gains

Mike,

You can have a calculated field that performs a table calculation:

Latest Score = LOOKUP(max([TABE Last Math Score]),last() )

Previous Score = LOOKUP(max([TABE Last Math Score]),last() -1)

Test Gain = [Latest Score]-[Previous Score]

The key is editing the table calculation once you place it in the view.

I updated your workbook with these formulas. Sheet 2 shows how you can use the test gain in a different visualization.

By they way, since you have duplicate records by client/date, I used Max(Score) instead of Sum(Score).

Is this what you were looking to do?

Pedro

• ###### 2. Re: Calculating test score gains

This is great! Thanks so much Pedro.

• ###### 3. Re: Calculating test score gains

I've got a similar situation but a bit more complicated. But still attempting to calculate gains. Can you help?