topic Re: Filter the rows in QlikView

Filter the rows

Thu, 31 Jul 2014 14:34:34 GMT

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

simenkg — Thu, 31 Jul 2014 14:37:00 GMT

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

alexandros17 — Thu, 31 Jul 2014 14:39:28 GMT

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

simenkg — Thu, 31 Jul 2014 14:41:41 GMT

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

c_gilbert — Thu, 31 Jul 2014 14:44:22 GMT

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.

Re: Filter the rows

Thu, 31 Jul 2014 14:55:29 GMT

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

c_gilbert — Thu, 31 Jul 2014 15:00:30 GMT

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

simenkg — Thu, 31 Jul 2014 15:01:41 GMT

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.