Set Analysis - always include a dimension and filter to only a specific value

[Anonymous]

Hi Qlikview Experts,

Experiencing some challenges with my set analysis if I am incorporating two conditions. For simplicity, my table has three columns. First column is CategoryA. Second column is CategoryB. Third Column is Data. It looks like this:

CategoryA, CategoryB, Data

ABC, Apple, 10

ABC, Apple, 2

ABC, Orange, 5

DEF, Apple, 20

DEF, Orange, 10

GHI, Apple, 20

GHI, Apple, 5

GHI, Orange, 5

I am creating a line chart with CategoryA as the dimension and sum of Data as expression. I also have a list box for CategoryA where the user can select only what dimension to display on the chart. However I want CategoryA = GHI to always display no matter what selection the user makes i.e. selecting ABC, DEF will still show a chart with dimensions ABC, DEF and GHI. This part I don't have any issues executing. However on top of that, I want to have two expressions (two lines on the chart) wherein one expression is fixed to only display Data where CategoryB = Apple and a second expression to only display CategoryB = Orange. Now this seems simple enough but I can't seem to figure out what I am doing wrong.

I first came up with this:

sum ({$+1<CategoryA={'GHI'},CategoryB={'Apple'}>}Data)

Using the above I noticed that the data filters for Apple only on the dimension of GHI. For ABC and/or DEF, it is ignoring that part of the formula and is including the Data for Orange.

I then modified it to the below:

sum ({$<CategoryB={'Apple'}>+1<CategoryB={'GHI'},CategoryB={'Apple'}>}Data)

sum ({$<CategoryB={'Orange'}>+1<CategoryB={'GHI'},CategoryB={'Orange'}>}Data)

The above seems to work perfectly; however, I noticed an issue when a user utilizes a List Box for CategoryB. If the user selects Apple from the List Box, I would expect that the entire line for Orange will disappear. Instead, the data for Orange only disappears for all dimensions except GHI. With Apple selected on the List Box and assuming all dimensions are selected on the CategoryA List Box, the resulting graph shows a line for Apple, and a dot for Orange above the GHI dimension. What I want to happen is if the user selects Apple, then the graph should only show the line for Apple.

Appreciate any help. Thanks!

[Gysbert_Wassenaar]

I then modified it to the below:

sum ({$<CategoryB={'Apple'}>+1<CategoryB={'GHI'},CategoryB={'Apple'}>}Data)

sum ({$<CategoryB={'Orange'}>+1<CategoryB={'GHI'},CategoryB={'Orange'}>}Data)

The above seems to work perfectly; however, I noticed an issue when a user utilizes a List Box for CategoryB. If the user selects Apple from the List Box, I would expect that the entire line for Orange will disappear. Instead, the data for Orange only disappears for all dimensions except GHI.

Actually, those expressions completely ignore any selection in both category fields. See attached qvw. Or am I totally misunderstanding you?

What I want to happen is if the user selects Apple, then the graph should only show the line for Apple.

If you want a selection in CategoryB to be applied to your expression then don't overrule that selection in the set modifier.

[swuehl · Accepted solution]

I think there is an typo in your expressions, the second field in each set expression should be CategoryA, right?

I think you can achieve what you want using the intersection operator *, something like

=sum ({$<CategoryB*={'Apple'}>+<CategoryA={'GHI'},CategoryB*={'Apple'}>}Data)

=sum ({$<CategoryB*={'Orange'}>+<CategoryA={'GHI'},CategoryB*={'Orange'}>}Data)

See attached Gysberts modified sample file.

[Anonymous]

I may have probably not been clear with the desired situation. The charts do not dynamically update based on the selections in Category. But thanks for the help.

[Anonymous]

Works perfectly! I do have another problem though. Although technically your corrected expression above works, it does not on how I have laid out my tables. I missed one detail that I did not perceive to be of importance on this case.

I actually do not want GHI to appear on the list box (since it is redundant as it is always included on charts by default anyway). To work around this, what I did was add another column (which I call _CatA) which mirrors CategoryA except that the value is blank where CategoryA = GHI. Using your revised calculation unfortunately also resulted in excluding GHI entirely on the charts if the user selects from _CatA listbox. I am not sure if can be fixed or if there is better way of hiding GHI from the listbox.

I want to attach the file here but can't seem to find a button to do this. I just see Insert Image/Video/Link.

[Anonymous]

Please see attached my document to better illustrate.

[swuehl]

Maybe like attached, using an expression in the list box:

=aggr(only({1<CategoryA -= {GHI}>}CategoryA), CategoryA)