Rolling Average, Currency Conversion, Pivot Table - not working together

[avastani]

I have a Pivot Table where the Months are across the top as a header instead of on the side as a row. I am using the RANGESUM(ABOVE(sum(Sales/IntermediateRate/TargetRate), 0, 3))

The user selects a TargetCurrency which gives them results in appropriate currency format. The problem I have is that the Rangesum to do the 3 month average sales amount is 0.00 when the Month-Year is across the top as a column header.

Is there a way to make the rolling average work when the Month-Year is a column header instead of the rows..

I would like to see,

Month-Year Jan-09 Feb-09 Mar-09 Apr-09

3mo Average(a) xxxx xxxx xxxx xxxx

monthly sale(b) xxxx xxxx xxxx xxxx

a/b xxxxx xxxxxx xxxxx xxxxx

and NOT,

MonthYear Expressions

Jan-09 3mo average xxxx

monthly sale xxxx

a/b xxxx

Feb-09 3mo average xxxx

monthly sale xxxx

a/b xxxx

any ideas are greatly appreciated. thanks much.

ps; i tried to do it in the script but wasn't able to achieve it because of the different currency rates applicable in each month for the (a) part of the expression.

[michael_anthony]

Will the "BEFORE" function work. It's equivalent to the ABOVE function, but works on columns to the left of the current column instead of rows above.

[avastani]

yes it does work, however, when someone transitions into a fast change to a line chart or straight table, i get 0.00 and nulls.

is there a way to determine the chart type in the expression?