2 Replies Latest reply: May 2, 2014 10:53 AM by Jonathan Dienst RSS

    How to creat dynamic expressions - Fieldvalues to a new Field?

      Hello Guys,


      My Name is Dennis from Germany, so sorry for my bad english



      I try to generate a dynamic chart table. In the dimension-tab are the facts. e.g. sales, turnover, amount of sales and so on

      The facts generated in a inline table in the script and looks like this:

      LOAD * INLINE [

          fact_ID, fact, fact_expression

          |10|, Sales, var_sales

          |20|, Turnover, var_turnover

          |30|, Amount, var_amount



      I try now to get the information out of the fact_expression field into a new field.

      So the global expresion converts from sum(fact_expression) to each row and get the Informations as a new Field and sum() the original field.


           Sales | sum(var_sales)

           Turnover | sum(var_turnover)



      My only successes were to change the one expression to sum($(=fact_expression)) -> But in a $(= ) QlikView lost the dimension-Row information and get all rows together in the fact_expression..

      My only chance at the momemt is to ask him what expression i want with set expression (e.g. over the fact_ID)


          $(= only({<





      Or  do select in a List box a fact or fact_id

      But that displays me only one and not all facts.




      So the goal: i want only one expresion which converts my fieldvalues into a real field to aggregate on it over sum..

      Hope you guys understand what my problem is..

      Maybe QlikView cant solve this problem at the moment.

        • Re: How to creat dynamic expressions - Fieldvalues to a new Field?
          Stefan Wühl

          But you want 1 expression, that kind of switches based on the current dimension value?


          Then you need to create something that will end up in an expression like


          =pick( match(Dimension, 'Sales', 'Turnover', 'Amount')






          You can create this expression in your script, there is an example in the forum how to do this, just can't find it right now.



          found one:

          How to make dynamic expressions work?

          • Re: How to creat dynamic expressions - Fieldvalues to a new Field?
            Jonathan Dienst



            Let me see if I understand you correctly - you would like to have a set of dynamic expressions calculated in a chart (or table) and you are trying to use set analysis to get the correct expression of each line based on a the expression number or name as a chart dimension?


            This won't work I am afraid, as the set expression is evaluated once for the chart before the dimensions are created and has no concept of the dimension. The way to solve this problem is to construct a Pick(Match()) type of expression. So let's say that you have the field 'fact' as a dimension. Then you would need an expression like:


                 Pick(Match(fact_ID, '10', '20', '30'),






            If you have a few expressions, then this is quite easy to create. If there are many expressions (I had up to 50 in a recent document), then you will need to construct the Pick(Match()) into a variable and then use




            to execute the variable.