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

    Filter the rows

      Hi,

       

      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!

       

      Tuan

        • Re: Filter the rows
          Simen Kind Gulbrandsen

          You need to use Set analysis.

           

          In the straight table expression use the following syntax:

           

          sum({$<Project_Current_Flag={'Y'}>}Amount)

           

          Regards
          SKG

            • 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

                screenshot.PNG.png

                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.