4 Replies Latest reply on Sep 27, 2013 7:29 AM by christophershanahan0

    Calculate Change in Grades Between Quarters and Filter for Changes




      I am trying to calculate the quarter over quarter change in grades and then display the results to be able to filter by changes for a particular quarter (3/31/13 and 6/30/13 in this example).  I have provided a simplified dataset for this problem.  The 'Raw Data' worksheet shows all of the data.  The worksheet 'Example Problem' shows the issue.  First of all, since the starting point is 12/31/12 and there is no data prior to then, it seems the result should come up as null for the previous rating for 12/31/12.  So perhaps the calculation for Previous Rating needs to be somehow changed to fix this issue.  Secondly, when I add a filter for quarter date the desired results do not come up and the ratings seem to get skewed.


      The results are that for 3/31/13 the grades for Eli and Sebastian improved and the results for 6/30/13 are  that Emma's grade declined and Eli's grade improved.  Ideally, I'd like to add a filter to show the rating changes by improve, decline, no change, but can't do that until the first part is correct.


      Thanks for any help anyone can provide.

        • 1. Re: Calculate Change in Grades Between Quarters and Filter for Changes
          Zac Hilbert

          One adjustment you have to make is how your table calculation is computed.  For [Previous Rating], right click the pill and click Edit Table Calculation.  For Compute Using, select Advanced, and put Day of Quarter and Rating in the Addressing box.  You can leave sort as automatic. Click Ok. For  At the level, use 'Deepest'. And set Restarting every to 'None'. This will make the previous rating for 12/31/12 null.


          I think the problem you will encounter when adding a filter for quarter date is that you will filter out the previous quarter values and will not be able to calculate the previous quarter rating because those ratings won't be in the data anymore. One approach you could look into is creating a parameter where you select the quarter you are interested in, and then create a calculated field that nulls the quarters you don't want to see (e.g. if [QuarterParameter] = [QUARTER] then [Rating] else null end).  Then use the calculated field for your display, but use the underlying field [Rating] for the table calculations.


          Hopefully this gets you pointed in the right direction.



          • 2. Re: Re: Calculate Change in Grades Between Quarters and Filter for Changes

            Thanks Zac, I made some progress using your suggestion and by also following an excellent post I found that was submitted by Joshua Milligan earlier this year http://community.tableau.com/thread/122740  I followed the solution he provided for a similar request which was to do the calculation computation at the level of "quarter" and restart every for every "student" .  Obviously, for the initial quarter for each student, Null would be the correct value for Prior_Rating_Outlook_Code.  From the data results in the attached, most of the time this was the case; however, there were many cases where the Null value was incorrectly showing up in the wrong location for some students (such as Amelia, Arianna, Austin, Benjamin, Blake, Caleb, Caleb, Chloe, Cooper, Eli, Elijah, etc.) and there was a prior rating code value for the first row of the time serios.


            I am really puzzled as to why this is occuring if the calc is supposed to be restarting for each student and how to correct this?  Please see the attached workbook update I did.  I am really stuck and would like to get a solution.


            I would note that for the Comparison calculation I used the calculation provide in Joshua's response tothe prior thread referenced above.  I'm not sure what the calc does, but I used it.


            Joshua Milligan

            • 3. Re: Calculate Change in Grades Between Quarters and Filter for Changes
              Zac Hilbert

              For you table calculation of Previous_Rating_Outlook, edit the table calc, for Compute Using, click Advanced... And then for the sort, instead of using Automatic, set it to sort by the minimum of QUARTER ascending.  The Automatic sort must have been sorting on database order, which is probably fine if your data are sorted. But not something you should count on...


              You'll need to do the same thing anywhere previous_rating_outlook_code is used. For example, the comparison calculation references this field. (So click on edit table calculation for Comparison and follow the steps above.)