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

Previous
Next Post »