topic sum from different pivot table in QlikView

sum from different pivot table

Sun, 17 Oct 2010 13:34:37 GMT

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

Miguel_Angel_Baeyens — Sun, 17 Oct 2010 16:17:27 GMT

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

Mon, 18 Oct 2010 06:15:30 GMT

Hi,

Thanks for your reply.

I tried it but it shows the value as zero?

Regards,

sum from different pivot table

Miguel_Angel_Baeyens — Mon, 18 Oct 2010 16:52:48 GMT

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

Tue, 19 Oct 2010 11:48:26 GMT

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

Miguel_Angel_Baeyens — Tue, 19 Oct 2010 19:14:38 GMT

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

Wed, 20 Oct 2010 07:10:22 GMT

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

Miguel_Angel_Baeyens — Wed, 20 Oct 2010 07:18:08 GMT

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

Wed, 20 Oct 2010 07:46:58 GMT

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.

sum from different pivot table

Miguel_Angel_Baeyens — Wed, 20 Oct 2010 08:33:01 GMT

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

Mon, 25 Oct 2010 06:52:08 GMT

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

Miguel_Angel_Baeyens — Mon, 25 Oct 2010 07:38:33 GMT

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

Wed, 27 Oct 2010 06:32:21 GMT

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

Wed, 29 Dec 2010 08:44:38 GMT

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

This allows for simplifiyng the data.