Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
Please explain me,
What is P() & E() and where should i use them?
- Thanks and Regards,
Villyee
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.
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.
P() - gives possible values, E() - excluded values.
They are used in Set Analysis.
You may find additional information in your QlikView Help.
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.
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
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
Hi Nizam,
It is possible to show this in a chart? if possible then can you give me a simple example.