topic Re: Refer to expression in other chart in QlikView

Refer to expression in other chart

Wed, 25 Nov 2020 16:16:04 GMT

Is it possible to refer to an expression in an other chart? I can't reuse the same expression.

Re: Refer to expression in other chart

Anil_Babu_Samineni — Wed, 03 May 2017 14:28:08 GMT

What is the question here, I would say yes it is possible to use

Re: Refer to expression in other chart

jonathandienst — Wed, 03 May 2017 14:36:19 GMT

If you mean a function or method to read the results in one sheet object (chart/table/list) in another sheet object, then no, that is not possible. You have to re-enter the expression in the second object.

>>I can't reuse the same expression.

Why not?

Re: Refer to expression in other chart

Thu, 04 May 2017 06:47:45 GMT

I will explain why I can't simply reuse the same expression with a simple example.

In the first chart, I divide sales by volume which gives me the unit price. I use a high level hierarchy dimension for this chart:

In the second chart, I also divide the sales by volume to get the unit price, but now with another dimension, which is more in depth than the dimension in the first chart:

Here comes the trick: I want to make an expression in the second chart that uses the unit price from the first chart (high level hierarchy).

Re: Refer to expression in other chart

avinashelite — Thu, 04 May 2017 06:49:50 GMT

One way to do this ...add the expression definition to a variable and use the same variable in different charts this is the way to reuse the expression in different charts

Re: Refer to expression in other chart

Thu, 04 May 2017 07:12:46 GMT

Thanks for your advice. How can I make sure that the variable is calculated based on product hierarchy level 1 when level 2 is also in the table?

Re: Refer to expression in other chart

avinashelite — Thu, 04 May 2017 07:49:58 GMT

In this case you need to have an AGGR() on the Level 1

Re: Refer to expression in other chart

Thu, 04 May 2017 08:21:13 GMT

Sorry I'm very new to QlikView, how should the code look?

Re: Refer to expression in other chart

avinashelite — Thu, 04 May 2017 09:21:02 GMT

aggr(expression,Level1) this will aggr the sum on the Level1 respective of the other dimensions

NOTE: you need to have Level1 as the dimension in the chart

Re: Refer to expression in other chart

Peter_Cammaert — Thu, 04 May 2017 09:43:13 GMT

You can reproduce the behavior of the first expression in the second chart using the TOTAL keyword and all dimension fields that should be ignored. See the aggregation function syntax here: Sum - chart function ‒ QlikView

However, it seems to me that summing the Fruit Sales and Volume values in the second chart doesn't produce values that match the same values in the first chart. Shouldn't they?

Re: Refer to expression in other chart

Peter_Cammaert — Thu, 04 May 2017 09:45:08 GMT

BTW if you use a pivot table, you don't need two objects. A single pivot table can produce different values depending on which levels are expanded.

Re: Refer to expression in other chart

Thu, 04 May 2017 13:19:50 GMT

You are right, the numbers that are summing the sales and volume should be the same as in chart 1 but I used random numbers so that's my mistake!

Thanks for the idea, I think that the TOTAL will work for me, do you know how I can use this in my expression when also working with variables to divide sales by volume?

Re: Refer to expression in other chart

Peter_Cammaert — Thu, 04 May 2017 13:30:31 GMT

I don't like copying the data from a screenshot. Can you post the output of your second chart as text (or an xlsx)?

Re: Refer to expression in other chart

Thu, 04 May 2017 13:34:30 GMT

Thanks! I now wrote this code in the variable:

aggr( $(v_Sales) / $(v_Volume), Product Hierarchy level 1)

This code works, but not anymore when I click on a Product Hierarchy level 2. Is there anything I can do to change this?

Re: Refer to expression in other chart

Peter_Cammaert — Thu, 04 May 2017 13:56:43 GMT

Ok, check this one out (multiple solutions):

Re: Refer to expression in other chart

avinashelite — Thu, 04 May 2017 14:20:42 GMT

You want to ignore the Product Hierarchy level 2 selection in the calculation ? then add the Product Hierarchy level 2 in set analysis with {<Product Hierarchy level 2,rest_filter >}

Re: Refer to expression in other chart

Thu, 04 May 2017 14:29:57 GMT

Yes that is what I want, where can I add this part of code to exclude level 2? It now looks like this:
aggr( $(v_Sales) / $(v_Volume), Product Hierarchy level 1)

Re: Refer to expression in other chart

avinashelite — Thu, 04 May 2017 14:41:57 GMT

v_Sales = you have the definition for this variable rite ? e.g sum(sales )? then try like this sum({<Product Hierarchy level 2=}Sale)