To avoid a ton of custom calculations, I can only think of unpivoting data to have a format like
Year | Participant_ID | Question_Nbr | Answer 2010 | 001 | 1 | 57 2011 | 001 | 2 | 62
This will still require custom calculations, but possibly only a few pounds.
An easy way to visualise the data without having to transform it how Dimitri suggested is just to put Measure names on Columns, Measure Values on Rows and Year on Color and Size. Then turn off stack marks.
It's not quite the same look as reference lines, but achieves much the same. You can also extend it to show more than 2 years - though you probably wouldn't want to go beyond 3 or 4.
Thank you for the feedback Dimitri and Richard.
Richard, I tried what you said. That strategy does result in a nice side-by-side bar chart, but unfortunately I am working with limited space and that format works less well.
Dimitri, I did as you suggested and unpivoted my data.
Unfortunately, I realized I do not have near as good of a grasp on reference lines as I thought I did.
It is easy to create a bar chart of this year's responses. The measure names go into columns, the measure values go into rows, and I set the measure to Average. Then, I can simply filter on year (Year = 2011).
Then, I try to create a calculated field for a reference line that shows the 2010 averages for the questions. I select a measure (one of the survey questions), then create a calculated field from it. I set that calculated field as the measure IF Year = 2010.
Here is where I get stuck. I want to actually display that value as a reference line, but just for the appropriate question in 2011. For example, if my graph shows the 2011 averages for Question A, Question B, and Question C, I want to display the 2010 average of Question A as a reference line only on the Question A column.
How might I go about this?
Thank you for your patience,
A quick example is probably the easiest way to explain. 2 sheets in the attached, one showing the layout I was talking about (which uses exactly the same screen real-estate as the one showing the reference lines).
I suspect what you were missing on the reference lines is that you need to set it to "Per Cell".
yearreflines.twbx 173.9 KB
Thank you for the example, Richard.
I am almost there.
How did you get the two bars to overlap?
I put measure in columns, the average of the measure value in rows, and the year in color and size. When I do this, the bars show as stacked (see attached).
2010_2011_yoy_example.png 15.0 KB
From the Analysis menu, select Stack Marks->Off.
The look may not be quite as nice as you can get with reference lines, but it doesn't need any transformation of your data structure or any calculated fields.
I would pick Richard's method as it is simpler and more flexible, without sacrificing much in clarity.
Kate, post here if you still need help doing it the "hard way".
Thank you Richard and Dimitri for your help. I got it working!