7 Replies Latest reply: Jul 31, 2014 11:01 AM by Simen Kind Gulbrandsen RSS

    Filter the rows



      I am new to QlikView, which I use to create a dashboard for managing budgets for projects. I have a dimension named "Project_Name", containing names of all projects (including historical ones), and another dimension named "Project_Current_Flag", indicating if the project is ongoing (by "Y" and "N").


      I created a straight table to show all details associated with each project, but I only want to display the ones that are ongoing (i.e. has a flag of Y). I tried the conditional display but the entire column just vanished. Can someone please help me out?


      Thanks in advance!



        • Re: Filter the rows
          Simen Kind Gulbrandsen

          You need to use Set analysis.


          In the straight table expression use the following syntax:





            • Re: Filter the rows
              Simen Kind Gulbrandsen

              Depending on the amount of data you have and the number of expressions, there are other solutions.


              One elegant solution could be to Define a new dimension in your script:


              if(Ongoing, Project_Name,null()) as Ongoing_Project_Name


              And use this in your chart with the box Suppress When value is null() in the dimension tab.

              • Re: Filter the rows


                Hi Simen,


                Thank you for the reply. My apologies for not making my requirements clear. Attached is the screenshot of my straight table. I would like to filter the rows such that only the projects that have a Y flag are displayed

                  • Re: Filter the rows
                    Charlotte Gilbert

                    Maybe try to just edit your dimension, so that it is a calculated dimension.


                    So instead of Project_Name as the dimension, instead have:


                    =if(Project_Current_Flag = 'Y', Project_Name)

                    • Re: Filter the rows
                      Simen Kind Gulbrandsen

                      You can use the logic as mentioned above.


                      As Charlotte says, you can give the user the choice using a List box.


                      If you want to lock the choice so that we always filter the Y Flag, the standard way to do it is by Set Analysis.

                      You should read up on it as it is a very valuable tool.


                      For the Spent-expression assuming it looks like Sum(Spent) today, the new expression would be Sum({$<Project_Current_Flag={'Y'}>}Spent)


                      This has to be done for every expression.


                      I do not recommend using a calculated dimension, as it reduces the performance on big data sets.

                  • Re: Filter the rows
                    Alessandro Saccone

                    Conditional display for the expression control the visibility of the field so, if it contain Y and N values the condition is not fullfilled and the field disappear.


                    To obtain what you want use a multiboc or a list box.


                    If it works, it means that data and object  are correct so if you want to write the condition in the dimension do:


                    If(Project_Current_Flag='Y', Project_Current_Flag, Null()) and check the box "Suppress null values"


                    The only thing: is there at least an expression? If not add one

                    • Re: Filter the rows
                      Charlotte Gilbert

                      Another way would be to add a separate list box, with the field as 'Project_Current_Flag' , then the users can choose between showing ongoing projects or ones that have finished.