3 Replies Latest reply: Nov 13, 2015 1:11 PM by Settu Periyasamy RSS

    Expression formula in script

    Ashish Palkar

      Hi There

       

      I am looking to write following expression formula at script level...but getting error..

       

      IF(Country='US','N/A',sum({$<MNTH={'-16'}>} [Revenue]))

       

      Thanks in advance

        • Re: Expression formula in script
          Oleg Troyansky

          This is caused by the numerous single quotes in your expression. When you try to assign this value to a variable, the quotes get messed up.

           

          There is a number of ways to overcome this problem:

           

          1. Replace single quotes with CHR(39) and concatenate them into your formula - this is quite ugly and messy.

          2. Use function REPLACE() and replace your single quotes with another unique character (for example, tilde ~) and then replace tilde with a single quote when assigning it to the variable.

          3. Export your multiple expressions into an external text file, then load the external file into QlikView and create variables dynamically using the peek() function.

           

          Check out my new book QlikView Your Business. In the book, you will learn and exercise with a lot of details, how to export your expressions into a text file and how to create them as variables.

           

          cheers,

          Oleg Troyansky

          Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

          • Re: Expression formula in script
            Stefan Wühl

            You can't use Set Analysis at script level and you need to use a GROUP BY clause when you are using aggregation functions.

             

            To help you with your request, you would need to provide more information about the context your expression is used in the chart (i.e. what are the dimensions used). If you are using fields in dimension / expression that are not located in the same table in the data model,  more detailed information about your data model would also be needed.

             

            Finally, since your sum() aggregation will react to changed selections  in the default set {$}, consider that aggregating values in the script provide static aggregates only.

            • Re: Expression formula in script
              Settu Periyasamy

              You can't do the set expression in Script.

              You need to do the Aggregation in script Like

               

              Load Dim,

                      sum(measure)

              Resident Table Group by Dim;

               

              Can you explain your need? or can you post the sample?