3 Replies Latest reply: Jan 15, 2015 1:12 PM by Srikanth P RSS

    Sommation in QlikView

      Dear community,

       

      In Excel, when you want to make a sum of a column (for example "Saldo"), with values in Cel A2 to A5,

      you can get this done with the formula "=Sum(A2:A5)".

       

      For Example:

      A2: 100

      A3: 200

      A4: 50

      A5: 50

       

      The formula delivers the value 400.

       

      Does such a formula exist in QlikView?

       

      The reason for this question is that without such a function, I would have to type A2+A3+A4+A5.

      When you are working with a limited number of values, this method is OK, but if you would like a sum of the values from A2 to A5000 for example, that makes a different story.

       

      First I thought the function rangesum would be useful. I wrote a code like:

      for i = 3 to 5000 step 1

      Load

      rangesum(Value2, Value$(i)) as Saldo

      Resident [TableName];

      Next i

       

      I thought that when for example i was 5, the result would be Value2+ Value3+Value4+Value5, but it is Value2+Value5.

       

      Any thoughts?

        • Re: Sommation in QlikView
          Peter Cammaert

          Aggregation in QlikView is usually done in the UI. There is a SUM() aggregation function that can be used in Text Boxes (to sum exeverything selected) or in an Excel Worksheet-like object like a Straight Table.

           

          Internal tables can be viewed as Excel Worksheets. They contain rows and columns. Load your data as-is, and aggregate in the User Interfac, just like in Excel.

           

          For example, if your field in the above example data is called... Example, load the data as-is and create a text box. In the expression field, type =Sum(Example).

           

          If you want to aggregate in the Load script, you won't be able to un-aggregate again later on. To sum values in your load script, use the GROUP BY clause and again a Sum() function to sum column values from different rows.

          • Re: Sommation in QlikView
            Ashwani Kumar

            In excel you have to mention the range to sum up.

             

            Here you have to just add the name of Column ,QlikView automatically sum all the records present in that field. These can be less in number or can be in millions..

             

            For this use script :

            Load

            field1,

            Field2

             

            from abc.xls ] ;

             

            Let Field2 is the name of Column which you want to sum, then use expression

             

            =sum(Field2)