I am using Tableau to help teachers analyse assessment data (see attached excel file).
There are three assessment areas - reading, writing and maths. The assessments are conducted annually - at this stage
there are three years of data. The scores ranged from 1 - 9, with 9 being the highest. I want to track the progress of students - both individually and as a group to see whether their scores are better, remained unchanged or worse as compared to the preceding year.
I have used the Reading assessment as an example.
I created the following calculation to measure progress from year to year:
Reading Progress Indicator#1 (Value) = [Reading (Y2)]-[Reading (Y1)]
Reading Progress Indicator#2 (Value) = [Reading (Y3)]-[Reading (Y2)]
Additionally I have created the following calculation to generate a progress descriptor:
Reading Progress Indicator#1 (Description) =
if[Reading Progress Indicator#1 (Value)]<0 then "Worse"
elseif[Reading Progress Indicator#1 (Value)]=0 then "No Change"
elseif[Reading Progress Indicator#1 (Value)]>0 then "Better"
The questions I have are:
1. There are instances where a student did not sit the assessment and therefore the value for that year is zero or nil. How would I modify the calculation to take this instance into account? For example if the student was not assessed in Year 1 but sat for the test in Year 2 and scored 6, the Reading Progress Indicator#1 (Value) calculation will produce a value of 6 and the Reading Progress Indicator# (Description) will show "Better" which is clearly incorrect. How do I get around this problem?
2. I want to calculate the percentage of students whose performance are worse, unchanged or better than last year. How do I go about this?
3. I would also welcome ideas on how the results can be presented to maximise visual impact.