4 Replies Latest reply: Oct 9, 2017 3:07 AM by Marcus Sommer RSS

    Excel based formula in QlikView

    Apoorva Dhulehole

      Hello Everyone,

       

      I have a question. Say, I want to create an expression, "ABCD" in my pivot table and the formula for calculating the expression is as shown in the attached image. Is it possible to write this kind of formula in QlikView which refers to values of next rows while calculating the value for that particular row.

       

      Picture.PNG

       

      I have attached the sample file also.

       

      Thank you.

        • Re: Excel based formula in QlikView
          Apoorva Dhulehole

          Any suggestion on this?

          • Re: Excel based formula in QlikView
            Marcus Sommer

            In general you could access other rows and columns within the pivot with interrecord-functions like above(), below(), before() ... but you will always need to define which row/column should be accessed related from the calling row/column which meant to define the proper offset of cells.

             

            As far as there is a rather simple logic to determine the offset it worked well and otherwise it will be quite difficult. You will never be so flexible in QlikView or any database like in excel because it always needs to be calculable. Therefore if the offset isn't rather simple you will need to calculate/transform your data within the script to your requirements.

             

            Beside them if I look on your example it might be possible to calculate the columns I and K with the help of a total-statement like: sum(TOTAL <Week> AnyValue): What does the TOTAL qualifier do?

             

            - Marcus

              • Re: Excel based formula in QlikView
                Apoorva Dhulehole

                Hi Marcus,

                 

                Thank you very much for your suggestion!

                 

                I tried using "below" function in the expression, but this works only when I select one Plant (like KBK in above excel sheet). When more than two plants are selected or none are selected then the expression doesn't work. Any suggestion?

                 

                [AOP Inventory monthly LC]/(below(I)/below(K))

                  • Re: Excel based formula in QlikView
                    Marcus Sommer

                    Your below-function has no second and third parameter to define the offset and therefore it's using as default-parameters just 1 and 1 and returning the values from one row below. Like above mentioned its not easy to define this offset if there isn't a simple logic or even a fixed offset. Qlikview and in general all databases aren't designed to solve data-queries in this way - it's quasi already an exception to the normal way of building a datamodel and to calculate results within the UI.

                     

                    This meant you might be able to get it working with a complex offset-calculation or even within a combination of some helping rows/columns and some total-calculations but it's far away from easy and performant and also the usability may suffer. Therefore try to solve it within the script or at least to transfer the most complex parts for it there.

                     

                    - Marcus