Limit dimension values displayed in QlikView Pivot Table Chart

Question

I have a pivot table chart in QlikView that has a dimension and an expression. The dimension is a column with 5 possible values: 'a','b','c','d','e'.

Is there a way to restrict the values to 'a','b' and 'c' only?

I would prefer to enforce this from the chart properties with a condition, instead of choosing the values from a listbox if possible.

Thank you very much, I_saw_drones! There is an problem I have though. I have different expressions defined depending on the category, like this:

IF( ([Category]) = 'A' , COUNT( {<[field1] = {'x','y'} >} [field2]), IF ([Category]) = 'B' , SUM( {<[field3] = {'z'} >} [field4]), IF (Category='C', ..., 0))) 

In this case, where would I add $<Category={'A','B','C'} ? My expression so far doesn't help because although I tell QV to use a different formula/calculation for each category, the category overall (all 5 values) represents the dimension.

Answer 1

One possible method to do this is to use QlikView's Set Analysis to create an expression which sums only your desired values.

For this example, I have a very simple load script:

LOAD * INLINE [
    Category, Value
    A, 1
    B, 2
    C, 3
    D, 4
    E, 5
];

I then have the following Pivot Table Chart set up with a single expression which just sums the values:

What we need to do is to modify the expression, so that it only sums A, B and C from the Category field.

If I then use QlikView's Set Analysis to modify the expression to the following:

=sum({$<Category={A,B,C}>} Value)

I then achieve my desired result:

This then restricts my Pivot Table Chart to displaying only these three values for Category without me having to make a selection in a Listbox. The form of this expression also allows other dimensions to be filtered at the same time (i.e. the selections "add up"), so I could say, filter on a Country dimension, and my restriction for Category would still be applied.

How this works

Let's pick apart the expression:

=sum({$<Category={A,B,C}>} Value)

Here you can recognise the original form we had before (sum(Value)), but with a modification. The part {$<Category={A,B,C}>} is the Set Analysis part and has this format: {set_identifier<set_modifier>}. Coming back to our original expression:

• {: Set Analysis expressions always start with a {.
• $: Set Identifier: This symbol represents the current selections in the QlikView document. This means that any subsequent restrictions are applied on top of the existing selections. 1 can also be used, this represents the full set of data in your document irrespective of selections.
• <: Start of the set modifiers.
• Category={A,B,C}: The dimension that we wish to place a restriction on. The values required are contained within the curly braces and in this case they are ORed together.
• >: End of the set modifiers.
• }: End of the set analysis expression.

Set Analysis can be quite complex and I've only scratched the surface here, I would definitely recommend checking the QlikView topic "Set Analysis" in both the installed helpfile and the reference manual (PDF).

Finally, Set Analysis in QlikView is quite powerful, however it should be used sparingly as it can lead to some performance problems. In this case, as this is a fairly simple expression the performance should be reasonable.

Answer 2

Woa! a year later, but what you are loking for is osmething near this:

Go to the dimension sheet, then select the Category Dimension, and click on the Edit Dimesnion button

there you can use something like this:

= If(Match(Category, 'a', 'b', 'c'), Category, Null())

This will make the object display only a b and c Categories, and a line for the Null value.

What leasts is that you check the "Suppress value when null" option on the Dimension sheet.

c ya around

Answer 3

Just thought another solution to this which may still be useful to people looking for this.

How about creating a bookmark with the categories that you want and then setting the expressions to be evaluated in the context of that bookmark only?

(Will expand on this later, but take a look at how set analysis can be affected by a bookmark)