Comparing change of values from groups (using the OVER function)

Suppose you want to know how much something change from one period to another for each category:

Case	Indicator	Time	Value
AAA	IndicatorA	2011	0.73
AAA	IndicatorA	2012	0.56
AAA	IndicatorB	2011	0.93
AAA	IndicatorB	2012	0.81
AAA	IndicatorB	2013	0.56
BBB	IndicatorA	2011	0.64
BBB	IndicatorA	2012	0.28
BBB	IndicatorB	2011	0.22
BBB	IndicatorB	2012	0.74
BBB	IndicatorB	2013	0.88
CCC	IndicatorA	2011	0.54
CCC	IndicatorA	2012	0.38
CCC	IndicatorB	2011	0.30
CCC	IndicatorB	2012	0.27
CCC	IndicatorB	2013	0.22
DDD	IndicatorA	2011	0.41
DDD	IndicatorA	2012	0.01
DDD	IndicatorB	2011	0.81
DDD	IndicatorB	2012	0.17
DDD	IndicatorB	2013	0.46

And you want to display this:

Case	Change 2011 - 2012
AAA	-0.17
BBB	-0.36
CCC	-0.16
DDD	-0.40

The over function will do the trick:

Sum([Value]) - sum([Value]) OVER (intersect([Indicator],intersect([Case],previous([Time]))))

Case	Indicator	Time	Value	Change
AAA	IndicatorA	2011	0.73
AAA	IndicatorA	2012	0.56	-0.17
AAA	IndicatorB	2011	0.93
AAA	IndicatorB	2012	0.81	-0.12
AAA	IndicatorB	2013	0.56	-0.25
BBB	IndicatorA	2011	0.64
BBB	IndicatorA	2012	0.28	-0.36
BBB	IndicatorB	2011	0.22
BBB	IndicatorB	2012	0.74	0.52
BBB	IndicatorB	2013	0.88	0.14
CCC	IndicatorA	2011	0.54
CCC	IndicatorA	2012	0.38	-0.16
CCC	IndicatorB	2011	0.30
CCC	IndicatorB	2012	0.27	-0.02
CCC	IndicatorB	2013	0.22	-0.05
DDD	IndicatorA	2011	0.41
DDD	IndicatorA	2012	0.01	-0.40
DDD	IndicatorB	2011	0.81
DDD	IndicatorB	2012	0.17	-0.64
DDD	IndicatorB	2013	0.46	0.30

• Tweet
• Share
• Share
• Share
• Share

About Admin MC3

This is dummy text. It is not meant to be read. Accordingly, it is difficult to figure out when to end it. But then, this is dummy text. It is not meant to be read. Period.

Related Post