Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
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
Highlighted
Champion II
Champion 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)

Highlighted
Contributor II
Contributor II

Re: P() E() inconsistent behaviour

second set should have 'Non personal'

Highlighted
Contributor II
Contributor II

Re: P() E() inconsistent behaviour

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

Highlighted
Champion II
Champion 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)

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Contributor II
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.

Highlighted
MVP & Luminary
MVP & Luminary

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