7 Replies Latest reply: Sep 2, 2016 12:01 PM by Andrew Walker RSS

    Already perform division in script ?

    Christina mueller

      Hallo QlikView Community,


      I want to calculate the sales margin by dividing two different numbers.

      Therefore I want to define a new field. Something like this:

      sales margin = sum(sales) / sum(profit)

      But because I have many different tables, I want to do it already in the script. Furthermore the answer should be in percent.

      Can anybody help me, because I have no Idea how to do it?


      Thank you!

        • Re: Already perform division in script ?
          Amit Saini



          Try something like below:



          load *


                 sum(sales) / sum(profit) as Value


          Resident TableA




          • Re: Already perform division in script ?
            Henric Cronström

            I don't think you really want to do this.


            If you define this ratio in the script, like amit.saini suggests, you will get number that looks OK (if you use a GROUP BY in the load). However, this is a static number that does not change when you make selections. But if you instead want a dynamic number that respects your selection, you should use sum(sales) / sum(profit) as a measure in an object.



            • Re: Already perform division in script ?
              Andrew Walker

              Hi Christina,

              I think the best idea would be to calculate such ratios in the user interface (BTW this is the ratio you may need: sum(profit)/sum(sales) .


              If you calculate them at record level (in the script) then you can't do much with them at a higher level. If you work out a margin at an order line level aggregating the values of these ratios is meaningless.


              If you create a chart which sums your sales and your profit then that is where you should put your sales margin calculation.


              Kind regards



              • Re: Already perform division in script ?
                Christina mueller


                Thank you for your quick answer!

                So the Problem is, that I have two different dimensions( please don't be disturb by the German !)

                On the one hand side I want to have the different Quartals and Budget and stuff like this . (column)

                But on the other side I also want to have different Values for order, sales, margin, profit etc.! (line)

                I already managed to do this with maaany if formulas, but if I then have to calculate something too, it is just getting to complicated and long...

                So if there is a formula like VLOOKUP in excel, that would also help me!

                For example: If the Title is 'sales' then pick the sales value

                Okay I hope somebody understands my problem ^^

                  • Re: Already perform division in script ?
                    Andrew Walker

                    Hi Christina,

                                        I can see a couple of different ways to approach this. If I understand your picture correctly you have four dimensions of which TGL_CS is the most interesting, this is the one that goes horizontally.


                    Does each value of TGL_CS correspond to a different calculation you want in the column? If so we can either:


                    1/ Remove this dimension and replace with 18 expressions.

                    2/ Keep the dimension and have one expression that will be a large Pick(Match( ...)) expression that will perform a different calculation for each value of TGL_CS.


                    Please check that I have understood this and then if you tell me the expression for [IST 2015 Monat] and [IST 2015 Monat ]I'll try to show what I mean for these two calculations then we hopefully add the others afterwards.


                    Kind regards



                      • Re: Already perform division in script ?
                        Christina mueller

                        Thank you for your answer!


                        The interesting point is not tgl but Titel!

                        One definition of one formula is now:

                        =Sum(if(Titel='Auftragseingang' and cat='VJ MS', [AE_Gesamt],
                        if(Titel='Umsatz' and cat='VJ MS',[Umsatz_Gesamt],
                        if(Titel='Auftragsbestand' and cat='VJ MS',[AB Gesamt],
                        if(Titel='Vertriebsspanne' and cat='VJ MS', VSP_Gesamt
                        if(Titel='VSP in %' and cat='VJ MS', VSP_Gesamt/Umsatz_Gesamt
                        if(Titel='Suko op/nop' and cat='VJ MS', [Sonstige Umsatzkosten (op/nop)]
                        if(Titel='Bruttoergebnis' and cat='VJ MS', [Bruttoergebnis absolut],
                        if(Titel='Bruttoergebnis in %' and cat='VJ MS', [Bruttoergebnis absolut]/Umsatz_Gesamt,
                        if(Titel='Vertriebskosten' and cat='VJ MS', Vertriebskosten,
                        if(Titel='Vertriebskosten in %' and cat='VJ MS', Vertriebskosten/Umsatz_Gesamt,
                        if(Titel='Vertriebskosten_op' and cat='VJ MS', Vertriebskosten_Op,
                        if(Titel='Vertriebskosten_N_op' and cat='VJ MS', Vertriebskosten_N_Op,
                        if(Titel='Sonstige Kosten' and cat='VJ MS',sonstige_Kosten,
                        if(Titel='Ergebnis' and cat='VJ MS', Ergebnis,
                        if(Titel='Ergebnis in %' and cat='VJ MS', Ergebnis/Umsatz_Gesamt))))))))))))))))


                        So it is quite huge! And some of them are even more complicated (the one with the %) Maybe I can do some matching, like you offered?

                        And than it would be easier to calculate in the formula?


                        Thank you very much!!

                        Kind regards


                          • Re: Already perform division in script ?
                            Andrew Walker

                            Hi Chistina,

                                                     That is a horribly large expression and you say that it is only one of them! I think your best friend here will be the Variable Overview found in the Settings Menu.


                            I would take out the and cat='VJ MS' parts and save this in a variable and call it something like vIfCatIsVJ_MS. This variable could then be referred to when cat='VJ MS' is true.


                            I don't know how familiar you are with the use of QlikView variables but when you've saved the definition of this variable you can then refer to the variable like so: $(vIfCatIsVJ_MS), called a dollar sign expansion (DSE).


                            You can create a different variable for each value of Cat and refer to each one by DSE and put them all on a straight table as separate expressions.




                            You can refer to a variable inside another .If Cat takes on, let's say three values (call them 'VJ_MS', 'Neil, 'Alex') you can have an expression like:


                            vWhatever = Pick(Match(Cat, 'VJ_MS', 'Neil, 'Alex'), $(vIfCatIsVJ_MS),$(vIfCatIsNeil),$(vIfCatIsAlex))


                            You can write the above as a nested if statement if you prefer of course. This variable vWhatever takes care of an array of possibilities, a range of values of Titel contained in your original expression and a range of values for Cat. Perhaps such an approach may help you to get a single expression you can use in a pivot table.


                            It's not possible to give firm advice when I know so little about your application but I hope this points you in a direction that will get you nearer your goal and not lead you into a blind alley.