Re: P() E() inconsistent behaviour - Qlik Community

d44973694 · ‎2017-09-04

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!

vinieme12 · ‎2017-09-04

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

d44973694 · ‎2017-09-04

second set should have 'Non personal'

d44973694 · ‎2017-09-04

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

vinieme12 · ‎2017-09-04

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

marcus_sommer · ‎2017-09-04

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 · ‎2017-09-04

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.

marcus_sommer · ‎2017-09-04

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