Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

d44973694
New Contributor II

P() E() inconsistent behaviour

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!

7 Replies
vinieme12
Esteemed Contributor II

Re: P() E() inconsistent behaviour

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)

d44973694
New Contributor II

Re: P() E() inconsistent behaviour

second set should have 'Non personal'

d44973694
New Contributor II

Re: P() E() inconsistent behaviour

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

vinieme12
Esteemed Contributor II

Re: P() E() inconsistent behaviour

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

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

d44973694
New Contributor II

Re: P() E() inconsistent behaviour

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

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