topic Re: Filtering a chart dimension when the expression uses set analysis in QlikView

Filtering a chart dimension when the expression uses set analysis

Ben_P — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Filtering a chart dimension when the expression uses set analysis

juraj_misina — Fri, 28 Sep 2018 08:33:48 GMT

Hi Ben,

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

Juraj

Re: Filtering a chart dimension when the expression uses set analysis

Ben_P — Fri, 28 Sep 2018 08:41:26 GMT

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)

Re: Filtering a chart dimension when the expression uses set analysis

juraj_misina — Fri, 28 Sep 2018 08:47:15 GMT

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.

Re: Filtering a chart dimension when the expression uses set analysis

Ben_P — Fri, 28 Sep 2018 08:57:56 GMT

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).

Re: Filtering a chart dimension when the expression uses set analysis

juraj_misina — Fri, 28 Sep 2018 09:13:50 GMT

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

Re: Filtering a chart dimension when the expression uses set analysis

Ben_P — Fri, 28 Sep 2018 10:29:33 GMT

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

Re: Filtering a chart dimension when the expression uses set analysis

juraj_misina — Fri, 28 Sep 2018 10:34:47 GMT

Glad to help.