topic How do i use the P() function? in QlikView

How do i use the P() function?

cclayford — Tue, 01 Oct 2013 12:38:21 GMT

I am currently using these expressions to calculate the following:

Sum for first 3 months:

sum({<Month={'>=$(=min(Month))<=$(=min(Month)+2)'}>}[Total Units])

Sum for last 3 months:

sum({<Month={'>=$(=max(Month)-2)<=$(=max(Month))'}>}[Total Units])

My dimension is [Meter Serial Number]

How would i use the P() function to only show the sum for the specific [Meter Serial Number] in the row?

Thanks,

Christopher.

Re: How do i use the P() function?

nizamsha — Tue, 01 Oct 2013 12:46:22 GMT

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.

SEE THE HELP FOR MORE INFORMATION

Re: How do i use the P() function?

cclayford — Tue, 01 Oct 2013 13:07:35 GMT

I tried this in my expression, but it does not bring back the desired result:

sum({$<[Meter Serial Number] = P([Meter Serial Number])>}{<Month={'>=$(=min(Month))<=$(=min(Month)+2)'}>}[Total Units])

Re: How do i use the P() function?

nizamsha — Tue, 01 Oct 2013 15:20:05 GMT

sum({$<[Meter Serial Number] = P({1<Month={'>=$(=min(Month))<=$(=min(Month)+2)'}>}[Meter Serial Number])>}[Total Units])

Re: How do i use the P() function?

swuehl — Tue, 01 Oct 2013 15:33:12 GMT

You want the set expression to consider the current dimension value? A set expression is only evaluated once, not per dimension value, you can compare it to a user selection that's specific to that aggregation, that defines the working set for that aggregation.

Please check this blog post, where you may find also a work around:

Evaluating "sets" in the context of a dimension