8 Replies Latest reply: Oct 15, 2015 4:02 PM by Jim Weiler RSS

    Selective filtering question

    Jim Weiler

      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
          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
              Jim Weiler

              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

                  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
                      Jim Weiler

                      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

                          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
                              Jim Weiler

                              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

                                  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
                                      Jim Weiler

                                      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!