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

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

    Dennis Schulze

      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)

      =sum(

          $(= only({<

              Fakten_ID={"|10|"}

          >}Fakten_Feld))

      )

       

      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')

          ,sum(Sales)

          ,sum(Turnover)

          ,sum(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.

           

          edit:

          found one:

          How to make dynamic expressions work?

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

            Dennis

             

            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'),

                      Sum(var_sales),

                      Sum(var_turnover),

                      Sum(var_amount)

                 )

             

            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

             

                 $(vPickMatch)

                

            to execute the variable.

             

            HTH

            Jonathan