3 Replies Latest reply: Aug 23, 2011 10:30 AM by Hans kleijnen RSS

    calculating gross margin

    Hans kleijnen

      Hi,

       

      I want to calculate a gross margin. I am using 2 tables:

      table1

      GLACCOUNTGLACCOUNTNAMEtype3
      10000sales prod 1revenu
      10001sales prod 2revenu
      10003sales prod 3revenu
      20001COS prod 1COS
      20002COS prod 2COS
      20003COS prod 3COS
      30001wageswages
      30002car leaseother
      30003fuelother
      30010lease buildingother
      30011stationaryother
      30012insurancesother

       

      table2

      GLACCOUNTGLACCOUNTNAMEAMOUNT
      10000sales prod 1-1000
      10001sales prod 2-1500
      10003sales prod 3-750
      20001COS prod 1500
      20002COS prod 2700
      20003COS prod 3100
      30001wages50
      30002car lease25
      30003fuel10
      30010lease building10
      30011stationary5
      30012insurances9

       

       

      how can i calculate the gross margin by using type3 in table1 (revenu - COS in money and in %)?

       

      thanks for your help.

        • calculating gross margin

          if you are sure your GLACCOUNTNAME field is always constructed in the same way, you can try the following:

           

          in the script

           

          Revenu:

          select substr(GLACCOUNTNAME, 7, 6) as product, amount as revenue

          from table1 inner join table2 on table1.glaccount = table2.glaccount

          where type3 = 'revenue'

           

          Cost:

          select substr(GLACCOUNTNAME, 5, 6) as product, amount as cost

          from table1 inner join table2 on table1.glaccount = table2.glaccount

          where type3 = 'COS'

           

          Qlikview will create two tables 'revenu' and 'Cost' and link them togheter on product.

           

          This will allow you to calculate the gross margin by product.

           

          Using a description to determine the product is not very safe, if you have a table wit the link between the GLACCOUNT and the product code, it would be better to use that

          • Re: calculating gross margin

            if you are sure your GLACCOUNTNAME field is always constructed in the same way, you can try the following:

             

            in the script

             

            Revenu:

            select substr(GLACCOUNTNAME, 7, 6) as product, amount as revenue

            from table1 inner join table2 on table1.glaccount = table2.glaccount

            where type3 = 'revenue'

             

            Cost:

            select substr(GLACCOUNTNAME, 5, 6) as product, amount as cost

            from table1 inner join table2 on table1.glaccount = table2.glaccount

            where type3 = 'COS'

             

            Qlikview will create two tables 'revenu' and 'Cost' and link them togheter on product.

             

            This will allow you to calculate the gross margin by product.

             

            Using a description to determine the product is not very safe, if you have a table wit the link between the GLACCOUNT and the product code, it would be better to use that