4 Replies Latest reply: Jan 25, 2016 7:40 AM by Tejinder Bamrah RSS

    Expression: Sumif

      Hi All - Newbie to forum (and qlikview)

       

      I have two tables Tab_1 and Tab_2, which are qualified (so that i can show all values in field even when selection is applied):

       

      Tab_1.Column1:

      Sales,

      Costs,

      Other

       

      Tab_2.Column1:

      Costs,

      Other

       

      i would like to insert a straight table in qv - using tab_2 as dimension column, and sum values where Tab_1.column1 values = Tab_2.Column1 values

       

      I have been looking around and have only got as far as the following - which seems to summing total value column in Tab1

      Sum({$<[Tab1_Column1]=([Tab2_column1])>}Tab1_Value)

       

      Any help would be much appreciated

       

      S

        • Re: Expression: Sumif
          Stefan Wühl

          I think you are showing only partial picture here, how are the two tables linked to each other?

           

          Could you post a small sample QVW?

          • Re: Expression: Sumif

            swuehl,

             

            many thanks for your reply:

             

            I have two tables: Tab_1 and Tab_2

             

            Tab_1.Capture PointValue

            Sales

            10

            Costs5
            Other2

             

            Tab_2 is:

            1) unlinked/Qualified (Very Important)

            2) was created as an inline table

             

            Tab_2.Capture Point
            Costs
            Other

             

            I have inserted a straight table in qv - the dimensions of which are those from Tab_2.

            I would like to sum from Tab_1 where values in Tab_1.Capture Point = values in the straight table (Tab_2.Capture Point)

            I would prefer to use the expression builder as a means of achieving this - basically, i need a sumif(tab_1.Capture Point,tab_2.capture point,tab_1.value)

             

            Please advise

             

            Many thanks

             

            S

              • Re: Expression: Sumif
                Stefan Wühl

                I would rather link both tables (or join them), so you can solve your table dimension filtering by just using the data model.

                (there are other methods to show all values, even when you made selections, using set analysis).

                 

                But even if you don't change your data model, using Tab_2.Capture Point as dimension and this as expression

                 

                =Sum(If([Tab_2.Capture Point] = [Tab_1.Capture Point], Value))

                 

                should work.

                 

                Tab_2.Capture Point Sum(If([Tab_2.Capture Point] = [Tab_1.Capture Point], Value))
                7
                Costs5
                Other2
              • Re: Expression: Sumif

                Perfect - didnt think the answer was use of an if statement

                 

                Many thanks for your help today

                 

                S