topic Re: P() & E() in QlikView

P() & E()

vanderson009 — Thu, 03 Apr 2014 11:03:28 GMT

Hello Community,

Please explain me,

What is P() & E() and where should i use them?

- Thanks and Regards,

Villyee

Re: P() & E()

MK_QSL — Thu, 03 Apr 2014 11:06:54 GMT

Set Modifiers with Implicit Field Value Definitions

In the above examples, all field values have been explicitly defined or defined through searches. There is however an additional way to define a set of field values by the use of a nested set definition.

In such cases, the element functions P() and E() must be used, representing the element set of possible values and the excluded values of a field, respectively. Inside the brackets, it is possible to specify one set expression and one field, e.g. P({1} Customer). These functions cannot be used in other expressions:

Examples:

sum( {$<Customer = P({1<Product={‘Shoe’}>} Customer)>} Sales )
returns the sales for current selection, but only those customers that ever have bought the product ‘Shoe’. The element function P( ) here returns a list of possible customers; those that are implied by the selection ‘Shoe’ in the field Product.

sum( {$<Customer = P({1<Product={‘Shoe’}>})>} Sales )
same as above. If the field in the element function is omitted, the function will return the possible values of the field specified in the outer assignment.

sum( {$<Customer = P({1<Product={‘Shoe’}>} Supplier)>} Sales )
returns the sales for current selection, but only those customers that ever have supplied the product ‘Shoe’. The element function P( ) here returns a list of possible suppliers; those that are implied by the selection ‘Shoe’ in the field Product. The list of suppliers is then used as a selection in the field Customer.

sum( {$<Customer = E({1<Product={‘Shoe’}>})>} Sales )
returns the sales for current selection, but only those customers that never bought the product ‘Shoe’. The element function E( ) here returns the list of excluded customers; those that are excluded by the selection ‘Shoe’ in the field Product.

Re: P() & E()

Thu, 03 Apr 2014 11:10:31 GMT

P() - gives possible values, E() - excluded values.

They are used in Set Analysis.

You may find additional information in your QlikView Help.

Re: P() & E()

manojkulkarni — Thu, 03 Apr 2014 11:17:13 GMT

Indirect Set Analysis

• p( ) = Possible

• e( ) = Excluded

Excluded

Possible

Customer = p({<Year={$(=max(Year)-1)}>} Customer)

Returns customers who had an association to last year.

sum(({<Year={$(=max(Year))},

Customer = p({<Year={$(=max(Year)-1)} Customer>}) Sales)

Returns this year’s sales for customers who had sales last year.

sum({$<CompanyName = p({$<CategoryName={'Baby Clothes'}>}

CompanyName)>} Sales)

Sales for Customers who purchased Baby Clothes.

The set p({}) returns the values of CompanyName (Customer) that

purchased Baby Clothes.

sum({$<CompanyName = p({$<CategoryName={'Baby Clothes'}>}

CompanyName) - p({$<CategoryName={'SportsWear'}>}

CompanyName)>} Sales)

Sales for Customers who purchased Baby Clothes but not

Sportswear.The first set p({}) returns CompanyName that purchased

Baby Clothes.The second set p({}) returns CompanyName that

Purchased SportsWear.The Unary Operator "-" between the two gives

a list of Customers (CompanyName)that belong to the first but not the

other set.

Re: P() & E()

arjunkrishnan — Thu, 03 Apr 2014 11:21:15 GMT

Hi Villyee Anderson,

What is P() & E() and where should i use them?

P() For Possible Value Example(=sum( {$<Cust= P({1<ProName={‘Jeans’}>})>} Sales ))

Here Customer-> Buy-> Possible Of Product (Jeans) ->SalesAmount

E() For Excluded Value Example(=sum( {$<Cust= E({1<ProName={‘Jeans’}>})>} Sales ))

Regards By

ArjunKrish

Re: P() & E()

nizamsha — Thu, 03 Apr 2014 11:30:21 GMT

suppose u have ten customer in 2014 march u r checking weather u have the same number of customer is present in the year 2013 march its called possible

excluded is a reverse of possible if u have 100 customer in 2014 march but in 2013 u have 1000 customer we will exclude the customer who present in the year both the year another than the customer we will take

for best eg: 2013 u have month from jan to dec and there sales is 100k

but for 2014 u have month from jan to april 10k

u can say that last year sales is high when compare to this year

so we want to check only for month jan to april for 2013 suppose u have 20k

now u can say last year sale is better than previours year for this we use possible ,exclude is a reverse of possible

Re: P() & E()

Wed, 08 Apr 2015 07:14:07 GMT

Hi Nizam,

It is possible to show this in a chart? if possible then can you give me a simple example.