13 Replies Latest reply: Dec 29, 2010 3:44 AM by msadrikhcb

# sum from different pivot table

Hi All,

I have two pivot tables in the same sheet.

the first pivot table is working fine, in the second table however, i need to calculate the sum using data from the first pivot table but i dont want all the information.

For example i have three expressions in table 1 and two in table 2.

in table 1 the expressions are as follows,

Currency, Net Assets, Position

in table 2 the expression that needs to be calculated is the net long assets wherein,If specific currenc

the above screen shot is from my excel file solution.

i have created the top table table as a pivot table, but need to create the second table where the net long position is to be calculated.

the net long position is calculated as follows, if the position (marked in red) is Long then add the values under the net open position ( marked in green).

Can anyone help me with this please?

• ###### sum from different pivot table

Hello,

Although I don't know your actual field names, your code might look like the following

`Sum({< Position = {'Long'}, Currency = {'EUR', 'GBP', 'AUD', 'INR', 'CHF', 'MYR'} >} Assets - Liabilities)`

Hope that helps!

• ###### sum from different pivot table

Hi,

Thanks for your reply.

I tried it but it shows the value as zero?

Regards,

• ###### sum from different pivot table

Hello,

It's possible I'm not hardcoding the conditions case sensitive, and you may have to write "LONG" instead of "Long", and likewise with the currencies.

Regards.

• ###### sum from different pivot table

Hi,

I did that the first timebut it still didnt work.

this is how i tried to them as also;

Sum({< [Position] = {'LONG'}, Currencies = {'EUR', 'GBP', 'AUD', 'INR', 'CHF', 'MYR'} >} [Net Open Position])

Sum({< [Position] = {'LONG'}, CURRENCIES.BRIEF_DESC_ENG = {'EUR', 'GBP', 'AUD', 'INR', 'CHF', 'MYR'} >} [Net Open Position])

but still i get a zero instead

Kind Regards,

• ###### sum from different pivot table

Hi,

Is Net Open Position a field or a formula you already have in one table?

On the other hand, both Position and Currencies must have case sensitive field names.

Hope that helps

• ###### sum from different pivot table

Hi,

Net Open Position is a formula

i have derived it as = [Assets] + [Liabilities]

I have used case sensitive field names

yet still the problem persists?

• ###### sum from different pivot table

Hi,

Just try

`Sum([Assets] + [Liabilities])`

it should return the same as in [Net Open Position] in the previous table. Then try

`Sum({< [Position] = {'LONG'} >} [Assets] + [Liabilities])`

where both [Position] and 'LONG' are correct, case sensitive field name and possible value, trying to find out where the problem is. If this doesn't work, please attach some example to see what may be happening.

• ###### sum from different pivot table

Hi,

I have tried it but still the zeroes persist.

I have attached the QV file.

Also under the table titles net long position, i have a dimension labeled as type, under it i want to have the following fields

- Net Position excluding USD

- Overall Position excluding BHD & USD

is this possible?

Thanks for your help.

Best Wishes & Regards.

• ###### sum from different pivot table

Hello,

First, use some existing dimensions. I've seen you were using "Type", but doesn't exist as dimension.

Then take the following expression as the Net Open Position for "LONG" in your second chart

`If(sum({< [BS Classification] = {'ASSETS', 'LIABILITIES'} >} YTD_CV) / 1000 > 0,sum({< [BS Classification] = {'ASSETS', 'LIABILITIES'} >} YTD_CV) / 1000)`

Bear in mind that you cannot "copy" expression names from a different table to another one, but the complete calculation (the expression) you have written. The formula above equals to saying "show Net Open Position when is greater than zero (Long)" as in your chart above.

To exclude USD from yoru chart, do another expression with

`sum({< [BS Classification] = {'ASSETS', 'LIABILITIES'}, CurDescription -= {'USD'} >} YTD_CV) / 1000`

And something similar adding BHD or any other currency you want to exclude in the new expression for Overall Position.

Regards.

• ###### sum from different pivot table

Hi Miguel,

Could you send me the file you hae worked on so I could see where i'm going wrong because it still doesn't work for me.

Thank you for your time and please bear a little with me.

Thanks Again.

Regards.

• ###### sum from different pivot table

HI,

Since some website error doesn't allow me to upload documents, please find it here. Once you have downloaded it, let me know so I can delete it from my page.

Regards.

• ###### sum from different pivot table

Hi Miguel,

Appoligies for late reply.

I downloaded the document, and went through it, i've managed to figure it out, but it was not what i was looking for, anyways thanks.

Regards.

• ###### sum from different pivot table

I have found the best way to use data from one table in another is through variables.

This allows for simplifiyng the data.