I am not sure if this is a Set Analysis Question or not.
Basically I need to subtract the current time period measure from the measure for every time period across the dimension.
I do not have the "Variance from Current Quarter" in my dataset. I need to calculate that.
| TimePeriod | Measurement | Measure | Variance from Current Quarter |
| CurrentQuarter | Coffee | 5 | 0 |
| Q2-2014 | Coffee | 4 | -1 |
| Q1-2014 | Coffee | 3 | -2 |
| Q4-2013 | Coffee | 3 | -2 |
| Q3-2013 | Coffee | 1 | -4 |
| Q2-2013 | Coffee | 0 | -5 |
| Q1-2013 | Coffee | 6 | 1 |
| CurrentQuarter | Tea | 4 | 0 |
| Q2-2014 | Tea | 11 | 7 |
| Q1-2014 | Tea | 1 | -10 |
| Q4-2013 | Tea | 9 | -2 |
| Q3-2013 | Tea | 8 | -3 |
| Q2-2013 | Tea | 2 | -9 |
| Q1-2013 | Tea | 0 | -11 |
| CurrentQuarter | Milk | 1 | -10 |
| Q2-2014 | Milk | 8 | 0 |
| Q1-2014 | Milk | 6 | -2 |
| Q4-2013 | Milk | 2 | -6 |
| Q3-2013 | Milk | 9 | 1 |
| Q2-2013 | Milk | 3 | -5 |
| Q1-2013 | Milk | 1 | -7 |
| CurrentQuarter | Sugar | 1 | -7 |
| Q2-2014 | Sugar | 8 | 0 |
| Q1-2014 | Sugar | 6 | -2 |
| Q4-2013 | Sugar | 2 | -6 |
| Q3-2013 | Sugar | 9 | 1 |
| Q2-2013 | Sugar | 3 | -5 |
| Q1-2013 | Sugar | 1 | -7 |
I took my crosstab table and made it longer by adding the Measurement Column, to take place of the data I recieved, which came to me like this:
| TimePeriod | Coffee | Tea | Milk | Sugar |
| CurrentQuarter | 5 | 4 | 1 | 1 |
| Q2-2014 | 4 | 11 | 8 | 8 |
| Q1-2014 | 3 | 1 | 6 | 6 |
| Q4-2013 | 3 | 9 | 2 | 2 |
| Q3-2013 | 1 | 8 | 9 | 9 |
| Q2-2013 | 0 | 2 | 3 | 3 |
| Q1-2013 | 6 | 0 | 1 | 1 |
If you can point me in the direction for the best way to do this, I would appreciate the help.
Thanks!
