1 Reply Latest reply on Nov 22, 2016 3:40 PM by D Barnetson

    Plotting Deviation from Target By Months From Start

    Ashwin Chandak

      Hello!

       

      I have attached a sample data spreadsheet that has data for 3 students A,B and C. Each student has a different join date. Furthermore, every month the students get graded in courses with a target of 75.

       

      I want to plot a trend line (line chart ) of deviation of the average of the students from the target ( of 75 ) every month from their join date.

       

      For example, in their first months A scored 70, B scored 50 and C scored 40 ( as shown in rows 4,6 and 8). The average would be 53.3 which is 21.7 below the average for the first months.

       

      I want the same logic to be replicated for each month the students are graded.

       

      Is there a way to doing this?

       

      Any workaround is welcome too!

       

       

      Thanks in advance.

       

      -Ash

        • 1. Re: Plotting Deviation from Target By Months From Start
          D Barnetson

          Hi Ashwin,

           

          Not sure if I'm 100% clear on what it is you are trying to achieve, but here is what I came up with. There may be something you can gain from it.

           

          From what I understand, you'd like to create a line graph that depicts the trend of deviation, based on the students total monthly averages, over time, starting from the first month each student started. (i.e.; The first month is equal to: A - Aug, B - September, C - October, and so on)

           

          Here is what I did first. Seeing Tableau works better as a presentation tool verses being a calculator, I created a new column in Excel to determine which month was each students first month, and so on. Numbering each month in order from the starting month. The formula I used is the following:

           

          'Month#'

           

          =1+SUMPRODUCT((A:A=A2)*(C:C<C2))

           

           

          This formula finds the students name in column A, then essentially ranks the months from first to last. See picture above.

           

          Now, I was able to bring the data into Tableau to create a line graph, showing the trend over time, starting with the average, deviated, starting from the first month of each student.

           

          The only calculation in Tableau that was required was the difference between the total average of each month and the Target. The formula is as follows:

           

          avg([Target])-avg([Score])

           

          Well, I hope this helps.

          Don