5 Replies Latest reply: Jun 26, 2013 1:20 AM by romaahuja RSS

    Intersection in set analysis

      Hi All,

       

      This is my first post on Qlikview community, I usually find the answers to all the questions I have on Qlikview community. I was using set analysis in qlikview and it wasnt giving me the ouptput that i thought it should. Basically, I have a simple table with name and value fiels as follows.

       

       

      Name
      Value
      n11
      n12
      n13
      n23
      n24

       

       

      Now I want to get the intersection and delta for the field values. That is I want the following:

      1. Union: Count of unique values that belong to n1 and n2 together (1, 2, 3, 4) =4

      2. Exclusion: Count of unique values that belong to only n1 (1, 2)=2

      3. Exclusion: Count of unique values that belong to only n2 (4)=1

      4. Intersection: Count of unique values that belong ONLY to to both n1 and n2 (3)=1

      5. Symmetric difference: Count of unique values that belong to both n1 and n2, but not to both (1, 2, 4)=3

       

       

      So I uses following set analysis:

      1. Count({1<Name={'n1'}> + 1<Name={'n2'}>}DISTINCT Value) 
      2. Count({1<Name={'n1'}> - 1<Name={'n2'}>}DISTINCT Value)
      3. Count({1<Name={'n2'}> - 1<Name={'n1'}>}DISTINCT Value)
      4. Count({1<Name={'n1'}> * 1<Name={'n2'}>}DISTINCT Value)
      5. Count({1<Name={'n1'}> / 1<Name={'n2'}>}DISTINCT Value)
      
      

       

      However, I am unable to get any result except the 1st result. I am getting the following results

      1. Count({1<Name={'n1'}> + 1<Name={'n2'}>}DISTINCT Value) --> 4
      2. Count({1<Name={'n1'}> - 1<Name={'n2'}>}DISTINCT Value) --> 3
      3. Count({1<Name={'n2'}> - 1<Name={'n1'}>}DISTINCT Value) --> 2
      4. Count({1<Name={'n1'}> * 1<Name={'n2'}>}DISTINCT Value) --> 0
      5. Count({1<Name={'n1'}> / 1<Name={'n2'}>}DISTINCT Value) --> 4
      

       

      I want to understand why these operations give misleading results, and what can I do to correct this? 

      I have attached my qvw file for refernce

      Any help is greatly appreciated.