topic Re: P() & E() and where do you use them? in QlikView

P() & E() and where do you use them?

Thu, 20 Dec 2012 15:39:51 GMT

What is P() & E() and where do you use them? and purpose of P() and E() ?

Re: P() & E() and where do you use them?

swuehl — Thu, 20 Dec 2012 15:54:29 GMT

You can only use p() and e() function in a set analysis set modifier.

From the Help file:

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.

Regards,

Stefan

Re: Re: P() & E() and where do you use them?

Anonymous — Sat, 08 Nov 2014 13:22:14 GMT

the use of P() and E() is a great functionality. Although i did not understand the part that I have copied below: what is meant by returns the sales for those customers that have supplied the product 'shoe'...? A supplier supplies a product and a customer buys it. May be I missed some thing. Can some one explain??

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.

Thanks again!!!

Re: P() & E() and where do you use them?

swuehl — Sat, 08 Nov 2014 14:23:46 GMT

There are at least four fields in that data model, Product, Supplier, Customer, Sales.

This example only makes sense if it's possible that values in field Customer, like 'Stefan', 'John', also appear in field Supplier. These entities than have two roles, they act as Customer as well as a Supplier.

Maybe it's not the best sample code to explain the p() function, but I think it's valid. It's there to explain that you can use the p() function to return possible values from a field (Supplier) other than the field you want to filter (Customer).

Hope this explains it a bit better.

Re: P() & E() and where do you use them?

Sat, 08 Nov 2014 14:51:21 GMT

Read below script

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

Indirect Set Analysis

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 (Company Name)that belong to the first but not the

other set

Re: P() & E() and where do you use them?

Sat, 08 Nov 2014 15:08:03 GMT

enti nagaraj see above script

if ur ok pls make it as a correct answer

Re: P() & E() and where do you use them?

MarcoWedel — Sat, 08 Nov 2014 15:30:44 GMT

Hi,

maybe this document and links help:

Set Analysis: syntaxes, examples

Re: Difference between P() and E() function?

Please Search before Asking a New Question!

http://community.qlik.com/blogs/communitymanager/2010/06/14/improved-search-comes-to-qlikcommunity

regards

Marco

Re: P() & E() and where do you use them?

Anonymous — Thu, 16 Jun 2016 06:29:55 GMT

Thanks !! understood what is P() AND E() tried to undertand it 100 times in past , may be today my state of

mind is correct or right that is why i can understand it is just nesting of a set analysis inside a set analysis .

Thanks Stefan

........................

Anant

Re: P() & E() and where do you use them?

Fri, 30 Sep 2016 08:15:15 GMT

very useful stefen. thank you

Re: P() & E() and where do you use them?

Anonymous — Fri, 13 Apr 2018 06:27:25 GMT

those last two links! So true.

Re: P() & E() and where do you use them?

Shubham_Deshmukh — Thu, 11 Oct 2018 13:32:52 GMT

That's great and simplest explanation that I ever learnt about P() and E() Stefan. Thanks for sharing this.

Re: P() & E() and where do you use them?

john_delligatti — Wed, 31 Mar 2021 12:24:08 GMT

Was just scratching my head over these exact scenarios and the P() function worked beautifully. Thank you!!