Hi,
Here's a fun one. I've got some survey data that I would like to display. We receive survey results every quarter and the user would like to see the last four quarters of survey results. Simple, but here's the twist: each quarter of survey result data must roll up the previous four quarters (including the quarter being analyzed) with the ability to drill down into the four quarters that make up that data. It's a bit hard to explain so I'll use an example. Say 2010 Q2 was my last reporting period - I would want to show the following (in a bar chart or pivot table):
2010 Q2 - Show count of surveys received from 2010 Q2 + 2010 Q1 + 2009 Q4 + 2009 Q3. Be able to drill into 2010 Q2 to see the breakout of each quarter.
2010 Q1 - Show count of surveys received from 2010 Q1 + 2009 Q4 + 2009 Q3 + 2009 Q2. Be able to drill into 2010 Q1 to see the breakout of each quarter.
2009 Q4 - Show count of surveys received from 2009 Q4 + 2009 Q3 + 2009 Q2 + 2009 Q. Be able to drill into 2009 Q4 to see the breakout of each quarter.
2009 Q3 - Show count of surveys received from 2009 Q3 + 2009 Q2 + 2009 Q1 + 2008 Q4. Be able to drill into 2009 Q3 to see the breakout of each quarter.
I don't know that this can be done with set analysis because the set is generated once per chart and I need a different calculation per row. I'd like to be able to do this all from my master calendar since it links nicely with the survey data. Has anyone run into this kind of problem before? Any help would be greatly appreciated.
I have attached an Excel document that contains some sample data and what the rolled-up results should look like. To summarize:
1. User should be able to pick the current reporting quarter
2. The last four quarters of data should be displayed
3. Each quarter is comprised of that quarter's data plus the preceding three quarters
4. The user should be able to drill into each quarter to see the four quarters that make up that data
Thanks!
