Refer to the calculated value of a field from another table

[waado]

Hi,

Is it possible for one table on the Qlik dashboard to refer to the calculated value from a different table. I mean to refer the calculated figure directly, without recalculating it. The reason I can't just recalculate it is that the level of granularity of the two tables is different, and that affects the calculation, and makes it so that the two tables do no have the same totals.

Please see the example below.

Table 1

Region	Asia	Asia	Asia	Asia	TOTAL
Block	Par1	Par1	Par2	Par2
Legal ID	A	B	A	B
Mort	1000	-500	400	100	1000
Cat	500	-200	350	90	740
MortCat	1118	-539	532	135	1246

Table 2

Region	Asia	Asia	TOTAL
Block	Par1	Par2
Mort	500	500	1000
Cat	300	440	740
MortCat	583	666	1249

The issue affects the MortCat row only. MortCat = if(Cat<0,-1,1) * Sqrt(Mort^2 +Cat^2).

The problem is that the negative values of in Table 1 disappear in Table 2. This is why using the same MortCat formula in table 2 gives incorrect the values in Bold. Those values should just be the sum of the values in Table 1 after dropping Legal ID. This is why I need to be able to refer to the calculated MortCat row from Table 1 and just do a simple sum in Table 2

Thank you!

[rwunderlich]

You cannot refer to the calculated value in another chart.  You can however recreate the same calculation using the Aggr() function to specify the dimensions used in the other chart. 

-Rob

[waado]

What should the syntax look like?

I tried Aggr ( if(Cat<0,-1,1) * Sqrt(Mort^2 +Cat^2), [Region], [Block]), but it did not help - gives the same answer

TryingAggr ( if(Cat<0,-1,1) * Sqrt(Mort^2 +Cat^2), [Region], [Block], [Legal ID]) breaks the expression completely, I suspect because [Legal ID] is not part of Table 2 dimensions

Something I didn't mention my original post is that Mort and Cat are expressions themselves, not straight numbers. And I am actually having a similar issue with the Mort field, where Table 2 does not show the simple sum from Table 1, however, I thought it would be best to focus on the separate MortCat issue.

[jcmachado · Accepted solution]

Yes, it is possible for one table on a Qlik dashboard to refer to the calculated value from a different table. One way to achieve this is to use a variable to store the calculated value from Table 1 and then reference that variable in Table 2.

Here is an example of how you could create a variable to store the calculated MortCat value from Table 1:

//Create a variable to store the calculated MortCat value from Table 1 Let vMortCat = Sum({$<[Legal ID]={'A','B'}>} if(Cat<0,-1,1) * Sqrt(Mort^2 +Cat^2));

This expression creates a variable named "vMortCat" that stores the sum of the MortCat values for Legal IDs A and B, calculated using the formula you provided.

You can then reference this variable in Table 2 to get the sum of MortCat values:

Sum({$<[Region]={'Asia'}, [Block]={'Par1','Par2'}>} $(vMortCat))

This expression uses the sum function to add the values stored in the variable vMortCat for the Region 'Asia' and Blocks 'Par1' and 'Par2'