4 Replies Latest reply: May 17, 2016 6:23 AM by Giuseppe De Vivo RSS

    Compare rows through Set Analysis.

    Giuseppe De Vivo

      Hi all,

      I need to compare different rows in the same table.
      For example, in the table [table1] I have this data:



      ID          VALUE          YEAR


      001          YES             2014 
      002          YES             2014

      003          NO               2014

      001          NO               2015

      002          NO               2015

      003          NO               2015



      I need to know "How many ID are YES in the Year 2014 and become NO in 2015".


      I have thought to use the Set Analysis because I'd like to avoid to create another table.

      I'm new about Set Analysis, but obviously I tried to do in this way:


      count(DISTINCT {1<YEAR={2014}, VALUE={'YES'}> * 1<YEAR={2015}, VALUE={'NO'}>}  ID)


      I have used the operator "*" to make an intersection between the two sets, but it doesn't work.
      Can someone help me to understand how I can change my expression?