topic Re: Selective filtering question in App Development

Selective filtering question

Wed, 14 Oct 2015 19:50:55 GMT

I have a question on filters. I have an app that has a number of gauges on it. These are filtered by client and month. What I'm trying to do is have a graph also that includes all months, or even a separate month filter. Everything seems to be automatically tied together and I'm not quite sure how to go about accomplishing this. I've figured out the {<Field=>} method to exclude filters from measures, but essentially what I [think I] need to do, is ignore the Month filter on the dimension of the chart. Since it's not wrapped in a Sum() or Avg() or another function, I can't figure out how to do that.

Ideally, I think I'd like the graph to obey the Client filter, ignore the Month filter, and have a Year filter that applies only to that graph. Any assistance would be greatly appreciated.

Thanks!

Re: Selective filtering question

Gysbert_Wassenaar — Thu, 15 Oct 2015 05:14:41 GMT

ignore the Month filter on the dimension of the chart

The usual way to do that is to add the record set definition to the measure(s) of the chart. If you really want to you can use a calculated dimension as dimension instead of a field. Something like Only({<Month=>}MyField)

Re: Selective filtering question

Thu, 15 Oct 2015 13:14:53 GMT

The usual way to do that is to add the record set definition to the measure(s) of the chart

I'm afraid I don't quite know what you mean by that.

I tried your other suggestion and it doesn't like that.

Only({<InteractionMonth=>}InteractionMonth)

This gives me an Invalid Dimension error

Looking up the help for the Only() function, it sounds like this is not what I'm looking for

Only() returns a value if there is one and only one possible result from the aggregated data. If records contain only one value then that value is returned, otherwise NULL is returned.

Re: Selective filtering question

Gysbert_Wassenaar — Thu, 15 Oct 2015 15:51:15 GMT

Sum(Sales) is an expression. Sum({<Year={2015}>} Sales) is a set analysis expression. You can use an expression as a Measure in Qlik Sense. {<Year={2015}>} is the part that makes an expression be a set analysis expression. It defines the record set of the expression.

Re: Selective filtering question

Thu, 15 Oct 2015 16:10:51 GMT

I tried that, but because the Month dimension has a filter applied, even though in your example the Sales measure would ignore it, the dimension still obeys it and the filtered out months are not present on the chart. What I basically ended up doing that seems to work is created a new Load script that mostly duplicates the first table but uses a different name for the Month column, and use that to populate the chart.

The set analysis is basically what my original goal was, but to apply that to the dimension in the same way as I did the measure. Without that, applying it to the measure doesn't achieve anything.

Re: Selective filtering question

Gysbert_Wassenaar — Thu, 15 Oct 2015 16:25:51 GMT

That can't be right. If all the measures in the chart object contain the set modifier then the dimension values will be limited by that.

Please post an example that exhibits the behavior your describe.

Re: Selective filtering question

Thu, 15 Oct 2015 16:43:49 GMT

Here you go. You can see the chart measure has the set modifier to ignore Month, but as soon as you select something from the Month filter, it applies it to the chart dimension.

Re: Selective filtering question

Gysbert_Wassenaar — Thu, 15 Oct 2015 18:53:52 GMT

Wow. That's very interesting. I think you've discovered a bug. Your filter pane contains an expression, i.e. it formats the Month values. That somehow seems to break the set analysis. If you add a filter pane with the unformatted Month field then selections do not influence the chart with the set analysis expression. And using {1} instead of {<Month>} also works even with the formatted Month filter pane.

Well, as a workaround you can create your formatted date in the script as another field or instead of the current Month field. The set analysis expression will work then.

Re: Selective filtering question

Thu, 15 Oct 2015 20:02:37 GMT

Aha! I've seen people referring to {#} in a lot of posts but I didn't know what to call it hence was not able to look up any help on it. I'm now looking through the QlikLearn Set Analysis doc and see that {1} appears to disregard ALL filters. In my actual report I am developing, I have 3 filters on the sheet. One of the measures needs to ignore only the Month filter, another ignores Month and Client, so I'm not quite sure what the workaround in that case would be using sets.

What I did do that seemed to solve the issue is format the month field in the load script rather than everywhere it was displayed. Which as I re-read your response seems to be exactly what you suggested.

Thanks very much for your assistance!