7 Replies Latest reply on May 30, 2012 4:45 AM by Jonathan Drummey

    Using Tableau to analyse class assessment data

    Hock Gan

      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.


      Thank You

        • 1. Re: Using Tableau to analyse class assessment data
          Jonathan Drummey



          Here are my best attempts at answering your questions:


          1. You need to specify what result you want those cases to have. Once you do that, you can have the calculations use an IF statement such as:


          IF NOT ISNULL([Reading (Y2)]) AND NOT ISNULL([Reading Y1)] THEN

              [Reading (Y2)]-[Reading (Y1)]

          ELSEIF ISNULL([Reading (Y2)] THEN

               //result for Null Reading Y2


               // results for Null Reading Y1



          2. Create a new worksheet and drag the Reading Progress Indicator #1 onto the Rows Shelf. It becomes a blue pill. Drag the Reading Progress Indicator #1 again onto the Rows Shelf. Right-click on this second pill and then choose Measure->Count, then right-click on the second pill again and choose Quick Table Calculation->Percent of Total. Then right-click on the pill once more and choose Compute Using->Table Down. This converts the second pill into an aggregate count measure to get the # of students at that status, then then turns the calculation into a % of Total. You can repeat this for the second Reading Progress Indicator, as I did in the attached workbook.


          3. First of all, what kind of story do you want to tell with these results? Secondly, the way your data is formatted makes it much more difficult to combine things. What you are really looking at is year over year progress and that's a lot easier to organize and display in Tableau when the years are members of the same dimension, instead of being located separate measures. Then, for example, you could have one Reading Progress Indicator calculated field. It would also be much easier to answer questions like how many students did not progress at all over the three years of data? And, finally, it would be easier to add another year's worth of data because you'd just be adding more rows instead of more columns. I suggest reshaping your data so there is one row per student/year/results. If you haven't read http://kb.tableausoftware.com/articles/knowledgebase/preparing-excel-files-analysis, it's a good place to start.





          1 of 1 people found this helpful
          • 2. Re: Using Tableau to analyse class assessment data
            Hock Gan

            Thanks Jonathan.

            I am particularly interested in your response to my third question. I followed your suggestion to organise the data where the years are members of the same dimension . See attached excel worksheet and packaged workbook. (I have made use of the original files but added a third tab to each document). You mentioned that I could have one Reading Progress Indicator calculated (as compared to the two in my original attempt). How do I go about formulating this calculated field?

            • 3. Re: Using Tableau to analyse class assessment data
              Jonathan Drummey

              Hi Hock,


              I've attached a workbook with a couple of ways to go at this. The first set of worksheets are:


              Difference from Prior

              Bar Chart

              Can't Make Crosstab


              In these I used the basic Quick Table Calculation to create the following calculations:


              Diff from Prior (Lookup) - uses the basic Difference from Prior Quick Table Calculation get the change in the Stanine field from previous.

              Reading Progress Indicator (TC) - uses the Diff from Prior (Lookup)


              With these, it's relatively easy to create a bar chart, and use a reference line to show the total, as in the Bar Chart view. However, when we want to show a crosstab, or a line chart, we run into problems because Tableau can't partition on table calculations, in other words Tableau is not able to use the results of a table calculation such as the Diff from Prior (Lookup) calculation as a way of breaking down the results of other calculations. (It can use the results of aggregation calculations like SUM, etc. but requires a little extra work to turn that on, and of course can use any unaggregated discrete value).


              When this happens, one general solution is to do the necessary aggregation in the data source and then provide that data to Tableau. So, I duplicated the reshaped data source and set it to Custom SQL to join the prior year's results in with the current year's results. Then, the Diff from Prior calculation is working on data within the same row and doesn't require a table calculation, or even an aggregate calc. The following worksheets show this:


              Join Prior Year - shows the raw data

              Crosstab - shows a crosstab

              Bar Chart - Join

              Line Chart

              Two Year Diff Workout Sheet - working out the two year calc

              Two Year Diff


              For the Two Year Diff worksheets, I created a calculation that uses the LOOKUP() table calculation to calculate whether a student had two years of successive declines or improvement, and bucketed everyone else in Mixed. This is the sort of calculation that is not really possible using the first example.


              Let me know if this works for you!



              • 4. Re: Using Tableau to analyse class assessment data
                Hock Gan

                Hi Jonathan,


                Many thanks for your help!


                It looks like what you have come up with is what I am after. However I would like to

                take time to get my head around the calculations that you have created for the worksheet. While I am comfortable using the basic calculation functions in Tableau, I do struggle with advanced functions such as Lookup as Custom SQL. Do you have any recommended sources that I can refer to develop my competency in using these functions?



                • 5. Re: Using Tableau to analyse class assessment data
                  Jonathan Drummey

                  Hi Hock,


                  Learning table calculations is a bit of a challenge. My suggestion there is to use these links:



                  http://www.tableausoftware.com/table-calculations (and follow all the links here, like the Top 10 Table Calculations)






                  And, practice, practice, practice. A lot of my learning came from going through forum posts and either a) looking through what someone else did to figure out what was going on, b) figuring out a solution myself to an answered question, then having the other answer to check my results against, and c) figuring out solutions to unsolved problems.


                  As for using Custom SQL, there are lots of web resources for learning the SQL syntax. As for learning the "when to use what method" part, I don't have a good resource. You could learn quite a bit about uses of Custom SQL in Tableau by searching the forums for posts with reshape/reshaping/reshaped or padding/padded/pad in the text, with particular emphasis on solutions by Joe Mako. Also, a lot of Joe's posts were before the new forums were implemented this past winter and are searchable on Google, which I find to be easier/better than the Tableau forum search function.


                  If you have particular questions, the forums are a great place to ask questions, and if you are particularly stuck we could do a webex and I could walk you through a solution.





                  • 6. Re: Using Tableau to analyse class assessment data
                    Hock Gan

                    Hi Jonathan,


                    Many thanks again for your help and advice. It is very much appreciated.