Need to Subtract Measure for Current Time Period from Measure for Previous

datagrrl — Tue, 26 Aug 2014 14:08:33 GMT

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:

If you can point me in the direction for the best way to do this, I would appreciate the help.

Thanks!

Re: Need to Subtract Measure for Current Time Period from Measure for Previous

datagrrl — Thu, 28 Aug 2014 15:11:53 GMT

I did it through some scripting. I don't think it was the perfect answer, but it works.