Creating an articial dimension entry using table concatenation...Help needed asap!

[#1]

Hi everyone,

I have a dimension called type which is created from two different fields. one of the fields is used to create the total which is the month end total value. this should, but often does not correspond with the other values. The pivot table i wish to create is shown below:

CaseType	America's	Asia	UK	Total
BF	180	100	5,867	7,736
NC	0	1	240	240
RS	(1)	(1)	(50)	(66)
OD	REQUIRED	REQUIRED	REQUIRED	REQUIRED
Total	175	99	5,953	7,854

I currently have BF, NC, RS and Total but I require OD. This is the difference between Total and the other rows.

the initial table without OD is created as follows:

MasterTable:
load
CaseType,
CaseValue,
Region
from abc.qvd;
load
('Total') as CaseType,
(Values) as CaseValue,
Areas as Region
from def.qvd;

Therefore we get the month total from the second table and the rest from the first table.

What I planned to do was as follows, basically create a table which would concatenate onto the existing table and thus include OD.

Upper('OD') as CaseType,
CaseValue,
Region
resident Mastertable
where CaseType = 'Total';
Upper('OD') as CaseType,
CaseValue * -1 as CaseValue,
Region
resident Mastertable

Where CaseType <> 'Total';

The reasoning behind this is that if Casevalue was therefore summed it the figures would give the difference between the two? However, this is not working. Has anyone got another approach as I've explored all avenues i can think of. I also tried,

sum({$<CaseType= {"Total"}>}CaseValue) - sum({$<CaseType-= {"Total","OD"}>}CaseValue)

This does not work in a pivot table as it does not recognise the set analysis for some reason. Any thoughts at all are most welcome.

Thanks,