Question regarding the use of p()

[rasmusnielsen]

Hi,

I have a question regarding the use of p() and what influences the actual set of possible values.

My data model consists of transactions that are linked to station id, product Id and a date. The station id is linked to a manager name.

I want to display a table with the transactions grouped on station level (Table C) and a table that displays the total aggregated values across all stations (Table A and B).

 

The total should disregard any field selections except for selections on date and product. ALL other fields should be ignored.

I my examples, I have selected a date interval of 1 week. This displays 98296 transactions as expected. But if I make a selection on a station or manager the total changes: 

In table A i am using:

Count({1<date=p(), productId=p()>}transactionId)

and in table B:

Count({1<date=p(), productId=p({$<stationId=>})>}transactionId)

As seen from the screenshots, table B is displaying the correct total, but my problem is that I don't want to add ALL the fields that I wish to ignore to the p() function.

Lets imagine I have zipcode, adresse, city, region etc linked to the station, and I don't want selections on these to influence my total. Then I would have to add them to the p() function as well.

As I understand, the p() is providing a set of possible values, but this set of values is influenced by whatever selection is made, and I need to specify what to ignore.

Also tried:

Count({1<date=p(), productId=p({1})>}transactionId)

but then my total won't react to selections in product id, which is one of the requirements.

I imagine that there is a simple solution, but I haven't been able to find it yet.

Thanks!

[Oleg_Troyansky · Accepted solution]

Hi there,

The function P() returns all possible values, and therefore it will be impacted by user selections in all those fields that you wanted to ignore. In order to ignore all selections, except for two fields, I'd use the following syntax:

Count({1<date=$::date, productId=$::productId>}transactionId)

Set Identifier 1 allows to ignore all user selections, while the two modifiers starting with $:: bring back the selected (as opposed to possible) values from the two fields that you wanted to account for.

To learn more advanced development techniques, including Set Analysis and AGGR(), check out the agenda of the Masters Summit for Qlik - coming soon to Orlando and to Dublin!

Cheers,

[rasmusnielsen]

Thank you Oleg!

Worked like a charm. Was not aware of the $:: syntax, I need to read up on the alternate states documentation 🙂

Br,

Rasmus