Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for
Did you mean:
Contributor

## Refer to the calculated value of a field from another table

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.

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!

Labels (1)
• ### Application Development

1 Solution

Accepted Solutions
Contributor III

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'

3 Replies

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

Contributor
Author

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.

Contributor III

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'

Community Browser