HI all,
I'm a bit stumped with how to create a month over month flux in my situation. Let me see if I can even describe it properly.
Every quarter we have accruals and then the accruals start over in the following quarter.
Jan would have one months worth of accrual. February would have a cumulative accrual of the Feb+Jan amounts. March would have the cumulative accrual for Mar+Feb+Jan. Apr would then start a new quarter so there is only one month of accrual.
Example
Jan - 10 (10 from Jan)
Feb - 15 (10 from Jan and 5 from Feb)
Mar - 22 (10 from Jan + 5 from Feb + 7 from Mar)
Apr - 8 (8 from Apr)
May - 14 (8 from Apr + 6 from May)
Therefore, I need to first calculate each months incremental amount and then compare it to the prior months incremental amount:
In Feb, I would want the MoM flux to show Feb = 5 vs Jan = 10
In Mar, I would want the MoM flux to show Mar = 7 vs Feb = 5
In Apr, I would want the MoM flux to show Apr = 8 vs Mar = 7
I could have just 4 input files: First month of quarter, second month of quarter, third month of quarter, first month of following quarter and number them 1 through 4. I did think I would then Union all 4 files together and then Crosstab the "month of quarter" column to get columns 1 through 4 with the amounts. Then I could use an Analytic App to allow the user to select the 2 periods they would like to compare. This is the point that I get stuck.
Am I completely approaching this incorrectly? Any help would be greatly appreciated! Thanks.
