8 Replies Latest reply: Sep 4, 2017 1:02 PM by Marcus Sommer RSS

    P() E() inconsistent behaviour

    Deepak Shrivastava

      Hi,

       

      We are facing an inconsistent behavior while using P()/E() functions.

       

      Requirement:

      We are trying to interact with our customers through various channels (widely categorized as 'Personal', and 'Non Personal' mod e of communication) .A customer can be engaged in either Personal, or Non Personal or Both type of interactions. If a personal is engaged/responds, the corresponding Engaged Flag is set to 'Y'.

       

      Requirement is to count all customers  who are:

      * Engaged only in 'Personal' interaction

      * Engaged only in 'Non Personal' Interaction

      * Engaged in both Personal, and Non Personal Interactions

      * Not Engaged in any interaction

       

      We are plotting a stacked bar chart for these categories by Month (MMM-YYYY).


      Issue

      when we select a month (DEC-2015) from filter, For Dec 2015 total customers engaged is 94812

      Personal = 34644

      Non Personal = 5184

      Combined (Both) = 3592

      Non engaged = 51392 (result in sum 94812 which is expected/correct)

       

      When no month is selected from the filter, result is displayed as 23235, 1658, 15001, and 51392 (TOTAL 91286, some values missing out or calculated incorrectly)

       

      Set expression used

      Personal: Count(Distinct  {<     ChannelType = {'Personal'},IsEngaged={'Y'}

                                          CustomerID=E({<ChannelType = {'Non Personal'}, IsEngaged={'Y'}>} CustomerID)          

                                  >}CustomerID)

       

      Non Personal: Count(Distinct {<     ChannelType = {'Non Personal'},IsEngaged={'Y'}

                                          CustomerID=E({<ChannelType = {'Personal'}, IsEngaged={'Y'}>} CustomerID)          

                                  >}CustomerID)

       

      Both: Count( Distinct {<     ChannelType = {'Personal'},IsEngaged={'Y'}

                                          CustomerID=P({<ChannelType = {'Non Personal'}, IsEngaged={'Y'}>} CustomerID)          

                                  >}CustomerID)

       

      NonEngaged = 1-Count({<IsEngaged={'Y'}>} Distinct CustomerID)/Count(Distinct CustomerID)

       

      I tried out for one month by modifying the expression as

      Count(Distinct  {<     ChannelType = {'Personal'},IsEngaged={'Y'}

                                          CustomerID=E({<ChannelType = {'Non Personal'}, IsEngaged={'Y'},MonthYear={'Dec-2015'}>} CustomerID)                                   >}CustomerID) and it yields correct results.

       

      Is P() calculation not checking only December data when plotted on a chart? Does it take the entire universe for checking intersection?

       

      Any help is appreciated.

       

      Thanks in advance!

        • Re: P() E() inconsistent behaviour
          Vineeth Pujari

          I don't understand what you are trying to achieve here

           

          just read out your set analysis

          Personal:

           

                    Count(Distinct  {<

                                  ChannelType = {'Personal'},IsEngaged={'Y'}

                                              << Return CustomerID  that have ChannelType =Personal and Engaged = Y

           

                                              CustomerID=E({<ChannelType = {'Personal'}, IsEngaged={'Y'}>} CustomerID)   

                                                      << here you contradict your earlier conditions : Return and EXCLUDE CustomerID  that have  ChannelType =Personal and Engaged = Y

                                 

                                      >}CustomerID)

           

           

           

          If you want to Test P() and E() , test them in separate expressions

          Example

                1)    Count(Distinct  {< ChannelType = {'Personal'},IsEngaged={'Y'} >} CustomerID )

           

                      using  P()

                  2)          Count(Distinct  {< CustomerID =P({<ChannelType = {'Personal'}, IsEngaged={'Y'}>} CustomerID)     

                                      >}CustomerID)

            • Re: P() E() inconsistent behaviour
              Deepak Shrivastava

              Please read it carefully. It has Personal in first condition, and Non Personal in possible/Exclusion

                • Re: P() E() inconsistent behaviour
                  Vineeth Pujari

                  ok, even with that what are you trying to achieve?

                  Do you only want to include Personal or do you want to exclude Non Personal ?


                  if you only want to include Personal then try either of the below, (1st is straight forward)

                     1)    Count(Distinct  {< ChannelType = {'Personal'},IsEngaged={'Y'} >} CustomerID )

                   

                              using  P()

                          2)          Count(Distinct  {< CustomerID =P({<ChannelType = {'Personal'}, IsEngaged={'Y'}>} CustomerID)    

                                              >}CustomerID)

                   

                   

                   

                  if you only want to Include NonPersonal then try either of the below, (1st is straight forward)

                     1)    Count(Distinct  {< ChannelType = {'Non Personal'},IsEngaged={'Y'} >} CustomerID )

                   

                              using  P()

                          2)          Count(Distinct  {< CustomerID =P({<ChannelType = {'Non Personal'}, IsEngaged={'Y'}>} CustomerID)    

                                              >}CustomerID)

              • Re: P() E() inconsistent behaviour
                Deepak Shrivastava

                second set should have 'Non personal'

                • Re: P() E() inconsistent behaviour
                  Marcus Sommer

                  I think Vineeth is right and you contradict your first conditions with the second one. Beside them I don't believe that you could be successful with this approach of set analysis and you would rather need something different maybe something like this:

                   

                  count({ < Field1 = {'Condition1'} > - < Field2 = {'Condition2'} > } distinct Field3)

                   

                  to cover the AND and/or XOR conditions between your dimension-values.

                   

                  If your data aren't really huge I would probably go with this way:

                   

                  count(if(aggr(concat(distinct ChannelType, ' + '), CustomerID) = 'Personal', CustomerID))

                  count(if(aggr(concat(distinct ChannelType, ' + '), CustomerID) = 'Non Personal', CustomerID))

                  count(if(aggr(concat(distinct ChannelType, ' + '), CustomerID) = 'Non Personal + Personal', CustomerID))

                  count(if(aggr(concat(distinct ChannelType, ' + '), CustomerID) = '', CustomerID))

                   

                  - Marcus

                    • Re: P() E() inconsistent behaviour
                      Deepak Shrivastava

                      Hi Marcus, Using P()/E() and +/- are resulting in the same issue unless I select a fitler. It seems that Possible ()/Exclude() part of the set considers  entire set (not on the dimension) unless we select a specific value in the dimension.

                        • Re: P() E() inconsistent behaviour
                          Marcus Sommer

                          If I think again about it I would say you couldn't use set analysis at all - because you want to consider the values of n records per customer but each record had just a single value - personal, non or NULL - and set analysis will only work on this record-level. This meant you will need to aggregate your values in some way before you could evaluating them if they met a certain condition - therefore take a look on my suggestion above with the aggr(concat()).

                           

                          - Marcus