Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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 currencerror loading image

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?

13 Replies
Miguel_Angel_Baeyens

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!

Not applicable
Author

Hi,

Thanks for your reply.

I tried it but it shows the value as zero?

Regards,

Miguel_Angel_Baeyens

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.

Not applicable
Author

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,





Miguel_Angel_Baeyens

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

Not applicable
Author

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?

Miguel_Angel_Baeyens

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.

Not applicable
Author

Hi,

I have tried it but still the zeroes persist.

I have attached the QV file.

[View:http://community.qlik.com/members/msadrikhcb/files/QV-problem.qvw.aspx:550:0]

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.

Miguel_Angel_Baeyens

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.