7 Replies Latest reply: Mar 10, 2015 5:35 AM by Imtiaz Ullah RSS

    How to filter a dimension

    Imtiaz Ullah

      Hi guys,

       

      I am having real trouble trying to simply filter a specific value from a dimension I am using inside a pie chart.

      I could recreate the dimension from the database but I am using this data elsewhere, so a simple filter within a particular visualisation is all I want to achieve.

      All I want to do is filter all records out where a particular column = 1

       

      pseudo code is:

      Filter all records where "mobile = 1", but display the 'mobiletype' column as the dimension inside the pie chart

       

      Is it me or is filtering in QS clunky?

       

      Thanks

      Immy

        • Re: How to filter a dimension
          Jonathan Poole

          Filtering is usually done by adjusting the 'measure' expression

           

          Say your Dimension is:   MobileType

          and your measure is:   sum(Sales)

           

          Adjust the measure expression to be:   sum( {<mobile = {1}>} Sales) 

           

          You can also use if() statements but the above method (SET ANALYSIS) is often cleaner

           

          ex;   sum(  if(mobile=1,Sales) )

          • Re: How to filter a dimension
            Aaron Morgan

            If you just want this individual chart to be filtered by that field, you can add it using set analysis, or as another option look at using an alternate state.

             

            As an example, your expression would look something like: Sum({<Mobile={1}>} Field)

             

            If you wanted the interactive ability to filter within the chart to a dimension that isn't actually used in the chart, using an alternate state is probably preferable. Create one, set the chart to be in this state and then create a list box and drag it on top of your chart for that functionality - or add it as a dimension if your data allows that.

            • Re: How to filter a dimension
              Imtiaz Ullah

              Hi Guys,

               

              I've tried all your suggestions but it's not working.

              All I am trying to do is a COUNT of records, excluding anything with a 1 on the Mobile column.

               

              Just refuses to work

                • Re: How to filter a dimension
                  Jonathan Poole

                  Can you post your QVF file ?

                   

                  Its saved under c:\users\<account>\documents\qlik\sense\apps  folder

                  • Re: How to filter a dimension
                    Michael Tarallo

                    Hi Imtiaz - can you see if this expression works for you:

                     

                    This uses Set Analysis - an introduction can be found here:

                     

                    Introduction to Set Analysis (video) - Part 1

                     

                    In regards to your example:

                     

                    Count({1-$<mobile_column={'1'}>}count_column)

                     

                    ...where mobile_column is the column containing the values you want to filter on and count_column is the column you want to count. The 1-$ will exclude the set of records that = to 1, therefore giving you everything but those records.

                     

                    Let us know how you do

                     

                    Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

                     

                    Regards,

                    Mike Tarallo

                    Qlik

                      • Re: How to filter a dimension
                        Imtiaz Ullah

                        So very odd behaviour, but got it working.

                        The data set only contains 1's or 0's (1 is mobile, 0 is not).

                         

                        When I set the filter to be 0 it works fine. When I set it to use 1 it always returned an empty result set.

                        In the SQL query, I CAST the output column and forced it to be an INT (in the DB it's a BIT!) and then QlikSense was able to filter on the 1.

                         

                        Is there something odd around handling of BIT values in QS?


                        This was my working statement in the by the way, which is pretty much what everyone suggested

                        COUNT({$<IsMobile={"1"}>}Id)

                         

                         

                        Thanks

                        Immy

                    • Re: How to filter a dimension
                      Imtiaz Ullah

                      Hi Mike,

                       

                      Whilst watching your 1.1 whats new video, I stumbled on that Set Analysis video (you should bump it up the list, it's hidden really low) and I am going to try it when I get to work tomorrow.

                      I followed the video and tried it on my home PC and it worked a treat, so I can only imagine I have something wrong, I think it's missing " around the INT value. I think I made an assumption as it was an INT that it wouldn't need it.

                      My testing @home proved I do and the video I watched says only dates do not need it.

                       

                      I'll update tomorrow.

                      Cheers

                      Immy