7 Replies Latest reply: May 22, 2016 8:49 PM by Sunny Talwar RSS

    How to use a filter in selecting graph data to display

    Harinder Bakhshi

      Hi,

       

      My first newbie question, I hope someone can help me

       

      I have two sets of data in a table:

       

      Masked Data

      Unmasked Data

       

      I also have a table with a single field 'Masking' and two possible values, 'masked' and 'unmasked'.  I created and loaded this table inline.

       

      Now I have a graph, if 'masked' is selected in the filter, I want my X axis to be avg(Masked Data).

       

      If 'unmasked' is selected, I want my X axis to be avg(Unmasked Data).

       

      If nothing is selected, then I want the X axis to use avg(Masked Data).

       

      What is the expression I would use to do this?

       

      Thanks

        • Re: How to use a filter in selecting graph data to display
          Stefan Wühl

          try a calculated dimension like

           

          =[$(=If(Only(Masking) = 'unmasked', 'Unmasked Data','Masked Data'))]

          • Re: How to use a filter in selecting graph data to display
            Sunny Talwar

            UPDATE: I just saw that you wanted that when nothing is selected, you see Masked data. I think I would suggest using 'Always One Selected Value in which case you can always default to Masked when the application is opened. Might be something you want to reconsider.

             

            UPDATE2: I have updated the sample in such a way that every time you press clear, the chart will show Masked Data. I did this by adding a clear state for the clear button, which will default  to Masked selection in the Selection field. Be cautious when setting the clear state because whatever selections you have made will be set when you set the clear state and the clear button will default to those selections. Ideally, I will clear out all selections except for Selection field and then set the clear state to make sure all else still behave as they would. To set clear state, look at the image below

             

            Capture.PNG

             

            Another approach could be like this:

             

            Script:

            Table:

            LOAD * Inline [

            Dim, Masked Data, Unmasked Data

            A, 10, 20

            B, 30, 10

            C, 40, 20

            A, 20, 32

            B, 43, 12

            C, 40, 23

            ];

             

            Dim:

            LOAD * Inline [

            Selection

            Masked

            Unmasked

            ];

             

            Chart Dimension -> I am using Dim, but you can continue to use whatever dimensions you have

            Dim

             

            Expression:

            =Avg($(='[' & Selection & ' Data]'))

             

            When you have Selected Masked in the Selection Filter, you will see the Avg of Masked

             

            Capture.PNG

             

            When unmasked is selected, you will see unmasked

             

            Capture.PNG

             

            The only thing to make sure here is that Selection has the option 'Always One Value Is Selected' checked out, because if it isn't, then there is a chance that the user might clear the selection in Selection or accidentally select both of them. In either of those cases, the chart would not be able to show any result.

             

            Capture.PNG

             

            Best,

            Sunny

            • Re: How to use a filter in selecting graph data to display
              Harinder Bakhshi

              Hi,

               

              Thanks for the tips.

               

              I should have mentioned that I am using Qlik Sense not Qlik view.

               

              I will try the suggestions and report back.

               

               

              • Re: How to use a filter in selecting graph data to display
                Stefan Wühl

                I've missed this part " I want my X axis to be avg(Unmasked Data)."

                 

                An axis should in general show something dimensional and not an aggregate (maybe you have some special needs, though).

                 

                But you can't just use an aggregation as dimension, you would need to use advanced aggregation to return at least point / line of the virtual table that the aggr() function creates:

                 

                =Aggr( Avg([$(=If(Only(Masking) = 'unmasked', 'Unmasked Data','Masked Data'))]), YourFieldToGroupTheAveragesBy)

                 

                and then you should define a measure for your chart.

                 

                I think it would be better if you can post a small sample QVF or some sample data records (e.g. in Excel sheets) and please elaborate further on your requested result chart.

                • Re: How to use a filter in selecting graph data to display
                  Harinder Bakhshi

                  Thanks guys,

                   

                  You all set me on the path to the right answer.  I'm afraid I cannot tick one as the Correct Answer as they all helped me get there.

                   

                  What worked for me in the end was:

                   

                  If(GetFieldSelections([Masking])='Unmasked',  Avg([Unmasked Data]),  Avg([Masked Data]))

                   

                  I've also condensed by table into a single field with a single value of 'Unmasked'.  This makes it work as an on/off switch with the default as off.

                   

                  I've just done the training but that was heavy on Tables and Load scripting and light on these expressions.  Thanks again for helping me fill in the gaps.