Crosstab: Variance on same item
My items are:
Left axis: Expense Reports.Department
Top axis: Expense Reports.Account
Invoice Distributions.Accounting Date - Year
Data Point: Expense Reports.Line Amount SUM
------------------------------
Therefore, my report comes over as (items in parantheses are just for labelling purposes):
001800 (Account#)
2010 2011 (Years)
3200 (Dept#) 1,000.00 1,500.00
3211 (Dept#) 750.00 1,000.00
3212 (Dept#) 500.00 500.00
-------------------------------
I'm trying to get:
001800 (Account#)
2010 2011 (Years) Difference
3200 (Dept#) 1,000.00 1,500.00 500.00
3211 (Dept#) 750.00 500.00 <250.00>
3212 (Dept#) 500.00 500.00 0.00
----------------------------------
I'm trying to create a variance column to provide the difference from the year 2010 to the year 2011. However, since these years are coming in from the same item (Invoice Distributions.Accounting Date - Year), I've read that I need to create separate items for each using a calculation. Does anyone know how to accompish this? I've tried using LAG, DECODE, etc and either those functions do not work or my calculation is missing something.