
1. Re: Plotting Deviation from Target By Months From Start
D Barnetson Nov 22, 2016 3:40 PM (in response to Ashwin Chandak)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