15 Replies Latest reply: Nov 22, 2017 11:55 AM by Rob Wunderlich RSS

    Qlik's issue with basic calculations

    Grzegorz Smajdor

      Hi,

      I use the September 2017 version.

      I found an  (funny?)  issue with basic calculations in Qlik Sense.

       

      Let's assume:

      a=1

      b=1+3

      result = $(a)/$(b)

       

      Now it's time for the question - result is equal.....

      Should be 0,25, right? (1 divided by 4)

      Not for QlikSense! For QlikSense the results equals 4.

       

      I started investigation how it's possible.

      It seems that Qlik instead of this:

      a) calculate a variable

      b) calculate b variable

      c) divide: a/b

       

      Does this:

      a) do not calculate a and b

      b) copy raw formulas (from variables) to the result: result = 1 / 1 +3

      c) calculate it right now..

       

      So the result is 1/1 +3 = 1+3=4

      Instead of 1/4...

       

      Is this a known issue?

      Are you (Qlik team) going to solve this problem? Maybe in the November 2017 version?

      Capture.PNG Capture2.PNG

        • Re: Qlik's issue with basic calculations
          Piet Hein van der Stigchel

          This is as intended, should you want the outcome to be 0.25 then use $(=a)/$(=b)

           

          The Little Equals Sign

          • Re: Qlik's issue with basic calculations
            Mohammed Mukram Ali

            Hi,

             

            The Order of Operations:


            "Parentheses, Exponents, Multiplication and Division, and Addition and Subtraction"

            The Expression should be like = $(a)/($(b))


            because you saved a and b values are in a string.


            a 1

            b 1+3


            the calculation will be

            =1/1+3


            first, it will divide 1/1 then it will do addition 3.

             

            While creating variable b you can use = symbol it will calculate the value ( =1+3 ) (4)

             




              • Re: Qlik's issue with basic calculations
                Grzegorz Smajdor

                1. It's not about the order of operations.

                It's about the process of calculation in Qlik Sense.

                 

                2. If you have

                x=2+3

                and you have to multiply 2*x

                what will you do?

                2*(2+3) or 2*2+3

                 

                Of course the first answer is correct.

                That's the logic of using variables/unknowns

                 

                The problem is that Qlik is using the second (wrong) way.

                 

                3.Using the equals sign in the definition of b variable is not a good choice.

                Maybe in this example it doesn't change too much.

                But - if the definition of "b" variable is more complex, the equals sign may have impact on the results of the "b" value.

                 

                4.  Of course, I can put everything into brackets, but I am not looking for "workaround".

                This should works correctly as in other softwares, scripting/programming languages etc.

                  • Re: Qlik's issue with basic calculations
                    Jonathan Vitale

                    I disagree that this is wrong.

                     

                    When you do a dollar sign expansion you are substituting text exactly as it is written. So if I have

                    a = 2+3

                    b = 2 * $(a)

                     

                    I SHOULD get 2 * 2 + 3 according to the rules of dollar sign expansion. This may not be what I intended (I might want 10 as my answer, and this will give me 7), but it is the consistent way that dollar sign expansions work. If you want 2+3 to be treated as a group, then use parentheses (2+3).

                      • Re: Qlik's issue with basic calculations
                        Grzegorz Smajdor

                        I understand the operation of $() expansion.

                        I said it wrong beacause it's illogical and unintuitive.

                         

                        If you define:

                        a=1

                        b=1+3

                         

                        The results of a/b will be:

                        in C# 0,25

                        in VBA 0,25

                        in JS 0,25

                        in Maths 0,25

                        and so on..

                         

                        in Qlik it's 4, cause it doesn't calculate varaibles before using.

                        The way of using variables in Qlik is opposite to standards and logic.

                         

                        And finnally it's ridiculous because to write correctly this simple expression:

                        a/(b+c/d)

                         

                        you have to use 37 characters

                        $(=$(a))/($(=$(b))+$(=$(c))/$(=$(d)))

                        and the expression is unreadable

                          • Re: Qlik's issue with basic calculations
                            Jonathan Vitale

                            I see what you're saying, I get it, but I I think that it does follow the logic of dollar sign expansion - which does not do any computation - it just replaces variables in a literal manner.

                             

                            By the way, one thing that has been overlooked in this discussion is "set" vs. "let":

                             

                            If you do the following

                            let a = 1;

                            let b = 1+3;

                            let c = a/(a+b);

                             

                            You will get the expected value for 'c', 0.20.

                             

                            Try it.

                              • Re: Qlik's issue with basic calculations
                                Grzegorz Smajdor

                                I see, but it's not related to data load editor. So the "let" and "set" keywords don't resolve the problem.

                                 

                                It's a little tricky and unintuitive.

                                Who wants to use variables as replacements of text formulas?

                                If you use e.g.

                                margin=0.1*Price-Discount

                                you want to keep results of the expression, not expression itself.

                                 

                                Who is interested in keeping text string "0.1 * Price - Discout" instead of exact number?

                                For me - more usefull (if we are talking about variables) is result, not the text of formula.

                                So the $(var) should return value, and more complex syntax like $(=$(var)) should return "text formula".

                                  • Re: Qlik's issue with basic calculations
                                    Rob Wunderlich

                                    Who wants to use variables as replacements of text formulas?

                                    If you use e.g.

                                    margin=0.1*Price-Discount

                                    you want to keep results of the expression, not expression itself.

                                     

                                    Using variables is probably 90% of the use of variables in Qlik.  The reason we want the text, not the result, is that evaluation must be deferred to the chart row where it will be calculated using the data for that row.

                                     

                                    Using your example above, we want the Price and Discount for the current row when we make the calculation.

                                     

                                    -Rob

                          • Re: Qlik's issue with basic calculations
                            Nacho Moreno

                            I think it is correct.

                            1/1 + 3 = 1 + 3 = 4

                          • Re: Qlik's issue with basic calculations
                            Lucke Hallbergson

                            Look at "let" vs "set" in help.

                            letset.jpg

                             

                             

                             

                            Let: Calculates the expression assigned to it and sets the expression result to the variable.

                            Set: Assigns the value(or expression which is after the equal sign) as it is without compute.

                             

                            https://help.qlik.com/en-US/sense/June2017/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/Set.htm

                             

                            https://help.qlik.com/en-US/sense/June2017/Subsystems/Hub/Content/Scripting/ScriptRegularStatements/Let.htm

                             

                            Regards

                             

                            Lucke

                              • Re: Qlik's issue with basic calculations
                                Grzegorz Smajdor

                                It not related to "let" or "set" keyword. It's not about "data load variables".

                                Please read the post before answer.

                                  • Re: Qlik's issue with basic calculations
                                    Rob Wunderlich

                                    I think you are mixing up a number of concepts and rules.

                                     

                                    As a best practice, you should always enclose variables that contain operators in parens to avoid order of operation issues. So recommended would be:

                                    a: 1

                                    b: (1+3)

                                     

                                    and then "$(a) / $(b)" would return the correct result.

                                     

                                    You made the assertion:

                                    ----------------------------

                                    if you define:

                                    a=1

                                    b=1+3

                                     

                                    The results of a/b will be:

                                    in C# 0,25

                                    in VBA 0,25

                                    JS 0,25

                                    -------------------------------

                                     

                                    That would be true if a and b were created using numeric expressions as you show in the example. You can do the same thing in Qlik Sense load script;

                                    a = 1;

                                    b = 1+3;

                                     

                                    Then the expression "a / b" would equal 0.25, as you are expecting.

                                     

                                    However, when you enter variable values using the variable editor, the values are always strings.  So if we return to the C#/JS example, the statements would be:

                                    a="1";

                                    b="1+3";

                                    a/b would not return a correct result.

                                     

                                    If you want to enter 1+3 in the variable editor, and you want it to be stored as the result of that expression, you can preceed the expression with an = sign, which will cause the result "4" to become the value of the variable. This is just as it is in your C#/JS example, you have to use an = if you want 1+3 to be treated as 4.

                                     

                                    -Rob