What is P() & E() used for in Qlik?

What is P() & E() used for in Qlik?

You might see this function and think we have entered into Statistical Analysis dealing with Expectation and Variance.

In Qlik, these are not Possible and Expected values, but instead, values that are Possible or Excluded are set by the selection parameters.

P() – Possible Values
E() – Excluded Values

In Qlik, we  only  use P() and E() functions in a set modifier within Set Analysis.

They are referred to as  Set Modifiers with implicit field value definitions.

In a scenario where we want to see Sales of motor vehicles in the current year, but in the analysis, when the user has selected a specific vehicle type, we would also like to know how the Sales of the specific model performed in the previous year.

Accomplish this with P() placed to evaluate the Previous Year Sales

Model=P({<Year={$(=max(Year)-1)}>}Model)

sum({<Year={$(=Max(Year))},Model=P({<Year={$(=max(Year)-1)}>}Model)>}Sales)

We are returning the possible values for the selected model in the previous year’s sales.

Similarly, using the E() will exclude sales of the selected model for the previous year.

Model=E({<Year={$(=max(Year)-1)}>}Model)

In the case where the requirement is to include or exclude specific models explicitly, you can enter these values into the Expression

Example:

Model=E({<Year={$(=max(Year)-1)}>}”Mustang”)

However, you can also achieve this by creating a variable in the load with the predefined values.

SET vExcludeModels = “Cobra, Mustang”; 

Model=E({<Year={$(=max(Year)-1)}>}$(vExcludeModels)) 

You can also create the variable from a comma-separated list of evaluated values from a previous LOAD statement. An example of this would be the dataset of Vehicle models that include a flag that is not displayed, for whatever reason, meets the business use case.

let vMax = NoOfRows('VehicleModels');

for vnum = 0 to $(vMax) step 1

let vExcludeModels= Peek('',$(vnum),'VehicleModels');

Set Modifiers with implicit field value definitions help separate information relevant to different departments or roles to predefine data relevant to the specific unit that the user operates in. Certain business units may have the same dataset available; this is another method to limit what a user has access to in certain circumstances.

It is also helpful for dashboards and displays that give comparative views of respective data values.

Up Next: Learn Qlik equivalent for Not Equal to (!=,NEQ,<>)