6 Replies Latest reply: Sep 3, 2016 2:55 PM by Thorben Ortmann RSS

    Sum selected values using concat and if

    Thorben Ortmann
      ID_ONEID_TWO ID_THREEIA

      1

      101001000
      2111012000
      2111023000
      2121034000
      3131045000
      4141056000
      415106

      7000

       

       

      Hello Dear Community,

       

      I am looking for a way to sum the complete IA of ID_One if ID_ONE is present in one row selected by if conditions.

      So first I want to selct the ID_ONE´s, which meet the If-conditions.

      Then I want to sum the IA in all rows, in which ID_ONE is present.

       

      Example:

      Distinct If ((ID_TWO=11 or ID_TWO=12 or ID_TWO=13 or ID_TWO=14)

      and (ID_THREE = 101 or  ID_THREE = 103 or ID_THREE = 105) ,ID_ONE) 

       

      This way I would get the ID_ONE´s  2 and 4.

       

      And then I want to sum the belonging IA. So the result should be ((2000+3000+4000)+(6000+7000)) = 22.000.

       

       

      To achieve this I thought of using Concat-function in combination with SubstringCount.

      I have provided an Example App below. The data is a bit different. I tried it like this:

      IF(SubstringCount(

      Concat( Distinct ID_ONE,';'),

      Concat(Distinct If([K_ONE]>2000 AND ([K_TWO]<2000 OR [K_Three]<>6) ,ID_ONE),';')

      )=0,

      Sum(IA))

       

      I would be grateful if someone could help me with that.

      Btw: I am using qlik Sense and the Limits for the if comparisions (11,12,13,14,2000, 6...) are actually Variables in my real Applikation.

       

      Best regards