5 Replies Latest reply: Apr 6, 2016 3:36 AM by Elmarie Blaauw RSS

    How can you use SUM(Column(n)) ?

    Elmarie Blaauw

      Hey there,

       

      I have a very complex set of calculations I am doing in a straight table. I made a simple example, as seen below, to explain my problem:

      Capture.JPG

       

      Column A is my dimension.

       

      Calculated expressions:

      Column D : column(1) * column(2)

      Column G : column(4) * column(5)

      Column H : column(3) + column(6)

       

      Column I : SUM(column(7)) / SUM(total column(7))

       

      With Column I the problem comes in, it does not calculate. When ticking the "Relative" box on the Expressions tab it gives the correct answer, but it does not allow you to use that percentage. Is there some way to solve this?

       

      Thank you,

       

      Elmarie

        • Re: How can you use SUM(Column(n)) ?
          Sunny Talwar

          It does not allow to use percentage? Can you share a sample?

          • Re: How can you use SUM(Column(n)) ?
            Settu Periyasamy

            Hi,

            it seems, sum function is not working with column() / label name of column. But Rangesum is working..

            e.g

                  sum(column(7)) -> gives 0  but rangesum(column(7)) gives values

             

            so, you can try like

             

            =[Total cost]/Rangesum(sum(TOTAL [2015 price]*[2015 Quantity]),
                                    sum(TOTAL
            [2016 Price]*[2016 Quantity]
            ))


            Here, [Total cost] is a Label name and check the Relative option..

            Capture.JPG

            Suggestion : Instead of using column(1), column(2) , You can directly use the label name.

            like [2015 price]*[2015 Quantity] (careful with your original field name)

             

            Sample Attached


            • Re: How can you use SUM(Column(n)) ?
              Prashant Sangle

              Hi,

               

              Use actual expression instead of column No or column Name.

               

              Regards,

              • Re: How can you use SUM(Column(n)) ?
                Jonathan Dienst

                The Column() function and references to the expression name operate on a strictly row-by-row basis. It is not possible to use multirow aggregations like Sum, but the inter-record functions Above() and Below() work. It is possible to use this  construct in place of Sum(Column(1)):

                 

                =Rangesum(Above(Column(1), 1, NoOfRows(TOTAL)))+RangeSum(Below(Column(1), 0, NoOfRows(TOTAL)))

                 

                (or with column names)

                 

                But it may be better to use the expression as max dreamer says, although you may need to wrap the expression in Sum(Aggr(...))

                  • Re: How can you use SUM(Column(n)) ?
                    Elmarie Blaauw

                    Like I said my calculations are very complex in the qvd I am developing with a vast amount of variables. So using the column names is not the solution. I just do not understand why QlikView does not have this functionality to use sum and column.

                     

                    On another note, thank you Jonathan! It works perfectly now.

                     

                    Thank you everyone for your help, really appreciate it!

                     

                    Kind regards,

                     

                    Elmarie