Sum if a value matches in Pivot Table using an unrelated Dimension

[salonibhatia]

Hi,

I have a transaction table, using 2 different dimensions say Region_a, Region_b of that transaction I have created a new dimension say Region. 

This new Region column is loaded as Inline(there could be a better way) script as follows:

Load Region_a

Resident Transaction;

Concatenate

Load Region_b

Resident Transaction;

The reason for doing this is that I need to create a Pivot Table which needs to have all the Regions and the Pivot table needs to be like below:

Region	Region_a Sales	Region_b Sales	Difference
A	20	30
B	40	10
C	60	30
D	70	70
E	65	60
F	55	-

Difference can be calculated once am able to calculate Sales. Is there anyway using Set Analysis I can write that if Region matched Region_a then give me sales or any other better method. I have tried Sum (if), Set analysis stating Region=Region_a but none of them are working. Strange thing sum(if) is not giving me correct data for all the regions. Any help would be great!

Thanks in advance!

[salonibhatia]

@swuehl , @sunny_talwar  Any suggestions?  I have found a fix by making Region columns as per the specific concatenated tables in the transaction but am not that happy with this fix, looking for a better solution. Please help 

[sunny_talwar]

Hi, I am not entirely sure I understand what you are trying to do. Would you be able to share the solution that you implemented which might give idea as to what you are trying to do.

[salonibhatia]

@sunny_talwar  Apologies for the late reply! Even though all the transaction tables are concatenated, there are some tables having salesamount for Region_a mapping and others having sales of region_b, so I created a common field region as below:

Transaction:

Load*,Region_a as region

Resident xyz.qvd;

concatenate

Load *,Region_b as region

resident gef.qvd;

I did this for all the tables concatenated and then I used the sum as required.

Earlier, I tried creating an island table which concatenated all the regions. After that I was trying to use sum(if) but that didn't work. Luckily the tables were little different and this method worked but, there would definitely be a better way. 

I also tried linking that data island but since some tables had Region_a and other had Region_b, it didn't work.

Hope this explains!