My goal is to have a four column report, as shown below, where it shows how each merchant (M1, M2, M3) has changed in behavior over time.
Example report in Excel: http://www.screencast.com/t/ShfeE3Gbyty
Note that the denominator for each period has changed and so the the Market Share difference is calculated as a simple difference between values.
Now it's my understanding that you can't calculate % values using a Table Calculation and then refer to those in other calculations, so I have been taking the approach of using window_sum() to calculate the %s myself.
But, it resolutely refuses to calculate properly. In my example, attached, I'm trying to get baseTotal to be 68 and compTotal to be 71, so that I can calculate the % of values appropriately.
However, my waning powers of deduction and inference are unequal to the task.
Ultimately I want the "% Change" Column to show the values as indicated in the screenshot (and Excel spreadsheet)
My thinking is this:
hoping one of your jedis out there can help a brother out