4 Replies Latest reply: Apr 2, 2017 5:46 AM by Qiyu Chen RSS

    Qlik calculated field

    Qiyu Chen

      Hi Guys,

       

      I am new to Qlikview/ Sense, I am trying to create a calculated field margin_per_unit (1st calculated field) and use that field to calculate the profit (2nd calculated field).

      However, it seems below script does not work. When create a KPI object, I expect the sum(profit) = (18-15)*10 + (7-5)*2 = 34. but the app shows as 0.

      Can you please assist?

      thanks.

       

      ++++++++++++++++++++++++++

       

      test:

      load *inline

      [

      cost,quantity,price

      15,10,18

      5,2,7

      ];

       

       

      test2:

      load

          cost,

        quantity,

          price,

          price - cost as 'margin_per_unit',

          'margin_per_unit' * quantity as profit

      resident test;

       

      drop table test;

        • Re: Qlik calculated field
          Ramzi Manoubi

          Hello. Following should work:

           

          test2:

          load

              cost,

              quantity,

              price,

              price - cost as margin_per_unit,

              (price - cost) * quantity as profit

          resident test;

           

          Also instead of single quotes,

          use double quote or square brackets when you have a space or a special character in a field name.

          • Re: Qlik calculated field
            Massimo Grossi

            you can't use an alias (bold) in the same load

            so this doesn't work

             

            test2:

            load

                cost,

              quantity,

                price,

                price - cost as margin_per_unit,

                margin_per_unit * quantity as profit

            resident test;

             

             

            you should repeat (price - cost) in the 2nd calculated field

                ..........

                price - cost as margin_per_unit,

                (price - cost) * quantity as profit

                .............




            another option, useful for many and/or comlex calculated fields is to use a preceding load  Preceding Load

             

            load

              *,

              margin_per_unit * quantity as profit;

            load

                cost,

              quantity,

                price,

                price - cost as margin_per_unit

            resident test;