4 Replies Latest reply: Sep 12, 2016 10:13 AM by Madeleine Josefsson RSS

    Margin %

    Madeleine Josefsson

      Hi,

      I would like to create a measure in my table that calculates the margin %.

      (Revenue-Costs) / Revenue

       

      My revenue is: sum({$<Account={3040,3042}>}ClaimCost)

      My costs are: 825 + sum({$<Account={4645,4643,4644,4646,4647,4648,4649}>}ClaimCost)

       

      But when I enter the following expression it does not work:

       

      (sum({$<Account={3040,3042}>}ClaimCost)-825+sum({$<Account={4645,4643,4644,4646,4647,4648,4649}>}ClaimCost))/(sum({$<Account={3040,3042}>}ClaimCost))

       

      Can I write it differentely?

        • Re: Margin %
          Robert Mika

          Try this:

           

           

          (sum({$<Account={3040,3042}>}ClaimCost)-825)+(sum({$<Account={4645,4643,4644,4646,4647,4648,4649}>}ClaimCost)/(sum({$<Account={3040,3042}>}ClaimCost))



          Qlik is very picky in term of using brackets

          BTW:

          I would use variables for your hard coded entry:

          825

          3040,3042,

          ...


          It will save lots of headache in the future.

          • Re: Margin %
            Sunny Talwar

            May be this:

             

            RangeSum(

                 Sum({$<Account={3040,3042}>}ClaimCost),

                 -825,

                 -Sum({$<Account={4645,4643,4644,4646,4647,4648,4649}>}ClaimCost)

            )/

            (Sum({$<Account={3040,3042}>}ClaimCost))

            • Re: Margin %
              Madeleine Josefsson

              How can I create a variable for my hard coded entry 825?

              Can I enter it to the script?

               

              The conditions are that if I have a value in my field 'FlagAvtal' then it should genereate 825, if I have two assignments with values in 'FlagAvtal' I should get 2 x 825 = 1650.