Filtering a chart dimension when the expression uses set analysis

[ben_pugh]

Hi everyone.

I have a chart that uses set analysis in the expression to show data for all Week values. Week is set to 'always one selected value', so the chart overrides this selection.

The problem I have is shown below. The chart has FieldA as a dimension, and shows data for all Week values. However the list box shows that there are only two values for FieldA in the currently selected Week.

I want the user to be able to highlight for example the first three FieldA values in the chart to narrow the selection. The problem I have is that when they do this, all filters on all other fields are cleared because there are no corresponding FieldA values for the currently selected Week.

Is there a way to solve this? I've tried applying set analysis to the dimension, but don't seem to be getting anywhere.

Thanks!

[juraj_misina]

Hi Ben,

can you post your expression here? This seems as if the set analysis contained something else as well.

Juraj

[ben_pugh]

I was simplifying a bit, the chart is actually slightly more complex.

There are two dimensions, firstly a cyclic group of [Field A] and [Field B], and secondly a drill-down group [Hierarchy].

The expression is as below. $(vWeek) is the currently selected Week.

Sum({$<Month=,Week={'<=$(=$(vWeek))'}>} St_Hrs)

/

Sum(TOTAL <[Hierarchy]> {<Month=,Week={'<=$(=$(vWeek))'},[Field A]=,[Field B]=>}Tot_Hrs)

[juraj_misina]

Sum({$<Month=,Week={'<=$(=$(vWeek))'}>} St_Hrs)

/

Sum(TOTAL <[Hierarchy]> {<Month=,Week={'<=$(=$(vWeek))'},[Field A]=,[Field B]=>}Tot_Hrs)

is the reason why you see all values for Field A. Based on your requirements you might want to put Tot_Hrs field to a separate table in data model so that you don't have to calculate it this way.

[ben_pugh]

Thanks Juraj.

I do want to be able to see all values of [Field A]. The problem is that I want the user to be able to click in the chart to make selections based on the [Field A] values that aren't in the currently selected Week (i.e., that are in grey in the list box).

[juraj_misina · Accepted solution]

Ah, I see. Sorry I didn't read carefully enough.

I'd try achieve this with alternate states. Try putting the Field A list box in another state, then you can use it in set analysis.

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Alternate%20States.htm

[ben_pugh]

Thanks Juraj. I've followed your suggestion and managed to get it working correctly.

[juraj_misina]

Glad to help.