11 Replies Latest reply: Nov 29, 2012 4:22 PM by Lav Jain RSS

    Exclusion in Set Analysis

    Miikka Koskinen

      Hi All!

       

      I'm making application that shows how often customers have bought things. I'll simplify it a bit to this. I need to groups, first is customers who have bought things in rolling 12 and second group is people who have bought things before this.

       

      I'm making this with set analysis and with QV9 sr5.

       

      Expression 1

       

      Count({

      (

      $<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>

      )

       

      }

      Distinct Customer)

       

       

      This gives me all customers who have bought things in selected rolling 12 period. Second expression is the problem:

       

      Expression 2

       

      Count({

      (

      $<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>

      )

      -

      (

      $<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>

      )

       

      }

      Distinct Customer)

       

      This gives me also customers who have bought things in rolling period. So I have customers that fits to both expressions. If I undestand second expression correctly then QV calculates 1-1=1. First part of expression returns 1 and second part of expression returns also 1.

       

      I have various versions of this:

       

      Version2:

       

      Count({

      (

      $<  Customer =

      P({$<Product={'Things'}, InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}>})>

      )

      *

      (

      $<Customer =

        E({$<InvoicingDate={">$(=v_Passive12)"}>})> 

      )

       

      }

      Distinct Customer)

       

       

      If I take version 2 apart then first part of expression returns 1 and second part of expression returns 0, but for some reason 1 * 0 = 1. I have tried also - and + operators in this.

       

      Any ideas?

        • Exclusion in Set Analysis
          Celambarasan Adhimulam

          Hi,

               This is the meaning of the operators.

              

          + Union. This binary operation returns a set consisting of the records that belong to any of the two set operands.

          - Exclusion. This binary operation returns a set of the records that belong to the first but not the other of the two set operands. Also, when used as a unary operator, it returns the complement set.

          * Intersection. This binary operation returns a set consisting of the records that belong to both of the two set operands.

          / Symmetric difference (XOR). This binary operation returns a set consisting of the records that belong to either, but not both of the two set operands.

          Example

          Set1={1,2,3,4}, Set2={3,4,5,6}

          Set1+Set2={1,2,3,4,5,6}

          Set1-Set2={1,2}

          Set1*Set2={3,4}

          Set1/Set2={1,2,5,6}

          Like this way you understood?

           

          Celambarasan

            • Exclusion in Set Analysis
              Miikka Koskinen

              Hi!

               

              I know meaning of operators.

               

              Have I understood how to define sets:

               

              Count({

              (

              $<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>

              )

              -

              (

              $<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>

              )

               

              }

              Distinct Customer)

               

              In that expression I think I define 2 sets and both return 1 and end result is 1.

               

              In this case:

               

              set1 = {1}

              set2 = {1}

               

              set1-set2 = {1}

               

              Or havent I understood how to define sets?

                • Exclusion in Set Analysis
                  Celambarasan Adhimulam

                  Hi,

                       You mean it return count as 1?

                   

                  Celambarasan

                  • Exclusion in Set Analysis
                    Celambarasan Adhimulam

                    Hi,

                         In expression2 - means

                         Get the records which satisfies this

                        

                    (

                    $<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>

                    )

                    and subtract the records which satisfies the below

                    (

                    $<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>

                    )

                     

                    Example:

                         v_Passive=Jan 2011,v_Passive12=Dec 2011

                         v_RollingStart=May 2011,v_MonthEnd=Mar 2012

                         Then Exclusion operator gives the result for

                         Jan 2011 to April 2011

                     

                    Celambarasan

                      • Exclusion in Set Analysis
                        Miikka Koskinen

                        Yes, I know what expression 2 means:

                         

                        Get the records which satisfies this

                            

                        (

                        $<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>

                        )

                        and subtract the records which satisfies the below

                         

                         

                        (

                        $<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>

                        )

                         

                         

                         

                        And this doesn't work.

                         

                        If I make 3 expressions to single table:

                        1. Count(($<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>) Distinct Customer)

                        2. Count(($<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>) Distinct Customer)

                        3.

                        Count(

                        (

                        $<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>

                        )

                        -

                        (

                        $<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>

                        ) Distinct Customer)

                         

                        All three will return 1.

                         

                        So first expression will return 1, second will return 1 and third calculates 1-1=1.

                          • Exclusion in Set Analysis
                            Celambarasan Adhimulam

                            Hi,

                                 Then have you checked with this expressions separately

                                

                            Concat({

                            $<  Customer =

                            P({$<Product={'Things'}, InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}>})>}Distinct Customer ,',')

                             

                            Concat

                            ({$<Customer =

                              E({$<InvoicingDate={">$(=v_Passive12)"}>})> }

                            Distinct Customer,',')

                             

                            You will know who all the customers are in common.

                             

                            Celambarasan

                        • Exclusion in Set Analysis
                          jagan mohan rao appala

                          Hi,

                           

                          Try this expressions

                           

                          Count({<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"} - {">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>} Distinct Customer)

                           

                          OR

                           

                          Count({<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>} Distinct Customer) -

                          Count({<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>} Distinct Customer)

                           

                           

                          Regards,

                          Jagan.

                            • Exclusion in Set Analysis
                              Miikka Koskinen

                              Hi!

                               

                              Your first expression gives same results than mine expressions: all customers who have bought things between Passive24 and passive12 including customers who have bought things in rolling period.

                               

                              Your second expression gives allmost correct results. I tried also that before I sended mail to here. Final result from that expression is -25124. Because that is normal minus-operation. First part will return number of customers who have bought things in passive-period (about 250 000) second will return number of customers who have bought things in rolling-period (about 270 000). Calculation goes like this: 250 000 - 270 000 = -20 000.

                               

                              I will (hopefully) get right result if I write that second expression like this:

                              =If(

                               

                              Count(

                              {

                              (

                              $<InvoicingDate={">$(=v_Passive24)<=$(=v_Passive12)"}, Product={'Things'}>

                              )

                              }

                              Distinct Customer)

                               

                              -

                              Count(

                              {

                              (

                              $<InvoicingDate={">$(=v_RollingStart)<=$(=v_MonthEnd)"}, Product={'Things'}>

                              )

                              }

                               

                              Distinct Customer)>0,1,0)

                               

                              Then I have to choose straight table and sum of rows as total mode.

                               

                              But this seems too complicated and I would like to get simple set expression right.

                          • Re: Exclusion in Set Analysis
                            Lav Jain

                            Hi Celambarasan,

                             

                            If : Set1={1,2,3,4}, Set2={3,4,5,6}

                             

                            Is there any difference between the results of :

                             

                            Set1/Set2  & Set2/Set1 ?

                             

                             

                            Regards