Calculations across pivot tables?

[#1]

I need to be able to perform calculations across dimensions within a pivot table; in this case the two dimensions are Baseline Officer Title and Current Officer Title.  

Basically I am performing a comparison between Current year and Prior Year in terms of timing.

In excel the calculation would be very simple here is the example:

Baseline Officer Title	MD	ED	VP	ASSC	Other	Total
Baseline	1,027	1,146	4,194	4,201	6,990	17,558
Curret	1,015	1,582	4,208	4,110	6,447	17,362
	12	-436	-14	91	543	196

The top line is the historic staff population using the Baseline officer title as a dimension.  The second line is the current staff population using the current officer title as a dimension. 

The calculation I need to perform is the difference between the two pivot tables as shown in the third line of the excel worksheet.

I can create these top two lines in two different QV pivot tables, but the issue I encounter is in attempting to calculate the difference between these two pivot totals.

I have attempted three different methods to address this calculation issue:

1. Using Set Analysis.
2. Creating new flags and dimensions at the database load / ETL level to identify differences between Baseline and Current officer titles.
3. Using if than else statements to explicitly perform the calculation differences between these dimensions

Also when I add both dimensions to the same pivot table the formating does not meet the requirements, in terms of presentaion and the subtotals are not100% correct.

None of these methods seem to work in the requried manner.

Has anyone else encountered this design issue and if so what was your strategy to resolve this problem?

[Miguel_Angel_Baeyens]

Hi,

Did you try using Dimensionality() in the expressions so when it equals to zero you set the expression for the total and otherwise you set the expression for dimension values?

If(Dimensionality() = 0,
  Sum({< [Baseline Officer Title] = {'Baseline'} >} Value) - Sum({< [Baseline Officer Title] = {'Current'} >} Value),
    Sum(Value)
)

Hope that helps.

Miguel