In a Pivot Table I need to calculate the net change between quarterly periods.

[#1]

In a Pivot Table I need to calculate the net change between quarterly periods.

I am having some issues with a particular calculation in a pivot table, using historical data over a Year and Quarter dimensions using Inter-Record Functions. 

I am essentially trying to use the QV Inter-Record Functions to determine the net change over time between quarterly totals, as shown  below in the screen shot below, in the Net Change (Net Chg. Column), after much trial and error, I have had some success.

I am using a combination of Set Analysis and Inter-Record Functions (After) to perform the calculation:

sum( {$<[Gender] = {'Female' }, [LTD Plan Indicator]={N}, [Benefits Indicator]={Yes}, [HR Event Id]={'HCT'} >} [Gender Inclusion] )
-After(Sum({$<[Gender] = {'Female' }, [LTD Plan Indicator]={N}, [Benefits Indicator]={Yes}, [HR Event Id]={'HCT'} >} [Gender Inclusion]), -1, ColumnNo())

The calculation works, except when it encounters, what I would call Quarterly boundaries between years. Essentially, between Quarter one of the current year and the prior year’s fourth Quarter, I get a null value, which I am displaying as Not Applicable (NA).  

In Pseudo code:

Quarter 1 (2013) – Quarter 4 (2012) = Net Change;   Result Null

Quarter 4 (2013) – Quarter 3 (2013) = Net Change; Result Correct

Quarter 3 (2013) – Quarter 2 (2013) = Net Change; Result Correct

Quarter 2 (2013) – Quarter 1 (2013) = Net Change; Result Correct

Would you have any suggestions how I could overcome this calculation limitation?   Thanks.

Additionally one suggestion that was brought to my attention would be to address this issue in the Load script, any suggestions how to accomplish this task?

[whiteline]

Hi.

Something like this:

=If(Quarter<>'Q1', <the expression above>, <the expression for the correct result for the first quarter>)