Limiting possible field elements with Set Analysis - P()/E()

emiledavis — Tue, 06 Oct 2015 16:51:22 GMT

This is admittedly not ground breaking, but it is worth sharing.

My data set has many fields available, and I only want territories that are in the East and West regions. Filtering out the territories that are not in the East or West regions within the script is not an option. So I must use some set analysis to handle this. At first, one might think to use SUM({<Region={'East','West'} Sales>}. The problem with this is that you wanted to see sales for just the East region, the chart would show for both East and West.

What I really wanted was to limit the possible territories for those that fall in the East and West regions. Using the P() function does exactly this.

SUM({<Region = P({<Region = {'East','West'}>})>} Sales>}

Dimension on the table is Territory

I can then create another chart to limit the possible territories to the North and South regions.

SUM({<Region = P({<Region = {'North','South'}>})>} Sales>}

Dimension on the table is Territory

The Exclusion E() can also be used to do the opposite. You can to limit the territories tho those that do NOT fall into the East or West Regions:

SUM({<Region = E({<Region = {'East','West'}>})>} Sales>}

Dimension on the table is Territory

As said before, this is not earth shattering, but I think there are intermediate users out there that might find this technique useful. The P() and E() functions come in handy at times.

Enjoy!

Emile

topic Limiting possible field elements with Set Analysis - P()/E() in QlikView

Limiting possible field elements with Set Analysis - P()/E()