Add filter pane to a single column in a table

[Ragnar]

Hi,

Looking for some assistance, please.

I have a table with a column that contains 'Yes' or 'No' which needs to be filter/listbox column so you can choose Yes or No and the respective rows will show Yes or No

This is the column (below) and this is the current expression I have: =if(column(1) + column(2) + column(3)=3, 'Yes', 'No') 

How do I make it a filter/list box in the expression and still keep the current expression I have above, please. 

Hope I made sense and thanks for your time.

[Andrei_Cusnir · Accepted solution]

Hello, 

If my understanding is correct, then you are using the function Column() in your straight table to generate a new column with values 'Yes' and 'No'. You would like to keep the expression for that column, but you would like to make the column filterable. So if you select 'Yes' then the straight table will show only the rows where the last column shows 'Yes' and if you select 'No' then the table will show only the columns where the last column shows 'No'.

Applying directly the filter on that column is not possible, because the column is a Measure and filters are applied to values that exists in your dataset. e.g. If you have a Field "Transportation" with values "Car, Bus, Plane, Train" etc. then you can select the value "Car" or "Bus" of the field "Transportation", however for Measures this is not possible, because you can have something "Sum(Sales)" which is a value that represents the summation of multiple values. For example you might have in the database the values 10, 20, 30 and the function Sum(Sales) will return the value 60. Trying to filter the value 60 is not possible as the value is not associated with any field of your dataset.

For example here is a Straight table from my reproduction:

This first column is a Dimension, the Column1, Column2 and Column3 are Measure columns, as well as Condition column. You will notice that "ID:" has a search option, because it is a Dimension and thus you can filter the values from the dataset, however the other columns doesn't have this option, because they are all Measure columns.

According to official documentation for Column() [1], it is stated that "The Column() function disregards dimension columns.", which means that in your use case scenario the "Column(1), Column(2) and Column(3)" are Measure columns and you are using a specific expression for each of them. If you would like to achieve your outcome, then you will have to use a different approach.

NOTE: Please keep in mind that there might be other ways to implement this, however this is the only way that I was able to make fully work.

Here are the steps that I have followed:

1. Instead of using the expression: "if(column(1) + column(2) + column(3) = 3, 'Yes', 'No')"
2. Use the expression "if(COLUMN_1_EXPRESSION + COLUMN_2_EXPRESSION + COLUMN_3_EXPRESSION = 3, 'Yes', 'No')"
3. This will give you the same result, but instead of using the function Column(), you will have to use directly the expression that is used for each individual column.

Now to limit the data and show only the rows with 'Yes' or only the rows with 'No', you will have to modify the expression in all the columns so that based on what the user selects, it will either show you the data or it will show you Null(). Because if the entire row is Null(), then Straight table chart will exclude that row entirely, with an additional setting. Here is the example that I have used based on the table in the screenshot above:

1. First created a variable:
2. 
3. We are going to assume that value 1 is for "Yes" records and value 0 is for "No" records.
4. Now create an Input variable:
5. 
6. For the fixed values use: "Label: Yes & Value: 1" and "Label: No & Value: 0"
7. Modify all the expressions in your Straight table Measure columns as:

=If(
    varSelected = 1,
    If(
        COLUMN_1_EXPRESSION + COLUMN_2_EXPRESSION + COLUMN_3_EXPRESSION = 10, 
        COLUMN_1_EXPRESSION, 
        Null()
    ),
    If(
        COLUMN_1_EXPRESSION + COLUMN_2_EXPRESSION + COLUMN_3_EXPRESSION < 10 or COLUMN_1_EXPRESSION + COLUMN_2_EXPRESSION + COLUMN_3_EXPRESSION > 10, 
        COLUMN_1_EXPRESSION, 
        Null()
    )
)

Allow me to break down this expression for you:

1. COLUMN_1_EXPRESSION, COLUMN_2_EXPRESSION, COLUMN_3_EXPRESSION are the actually expressions that you have used in your columns to create them.
2. The first if statement will check if the user wants to display all the 'Yes' values or all the 'No' values
3. If user has selected 'Yes' from the Input variable, then the first inner If() statement will be evaluated. So it will check if the sum of the first 3 expressions equals to 10, then it will return 'Yes', otherwise it will return Null()
4. If the user has selected 'No' from the Input variable, then the second inner If() statement will be evaluated. So now we have to reverse your initial expression to check for all the 'No' values. This means that we need to see if the sum of all the expressions is less or grater than 10, then we will return 'No' value, otherwise we will return Null() value.
5. In your use case scenario, you will have to change the numbers 10 (in the If() statements ) with number 3.
6. Additionally, for each column you will have to include exactly the same expression, and the only part that is changing is the section in RED. So in that case you replace it with the expression that you want to display and for the last column the expression should look like:

  =If(
    varSelected = 1,
    If(
        COLUMN_1_EXPRESSION + COLUMN_2_EXPRESSION + COLUMN_3_EXPRESSION = 10, 
        if(COLUMN_1_EXPRESSION + COLUMN_2_EXPRESSION + COLUMN_3_EXPRESSION = 10, 'Yes', 'No'), 
        Null()
    ),
    If(
        Sum(COLUMN_1_EXPRESSION + COLUMN_2_EXPRESSION + COLUMN_3_EXPRESSION < 10 or COLUMN_1_EXPRESSION + COLUMN_2_EXPRESSION + COLUMN_3_EXPRESSION > 10, 
        if(COLUMN_1_EXPRESSION + COLUMN_2_EXPRESSION + COLUMN_3_EXPRESSION = 10, 'Yes', 'No'), 
        Null()
    )
)

This will allow you to show the word 'Yes' or 'No' in the last column. So far you will end up with a table similar to:

As you can see the table shows the values only where the row has 'Yes' in the last column and the rest are Null(). You can then go to Table's properties and uncheck the option "Include zero values" under "Add-ons > Data handling"

So now when 'Yes' is selected:

Because 6+2+2 = 10 and 5+2+3 = 10 etc.

While when 'No' is selected:

Because 15+2+4 != 10 and 7+2+3 != 10 etc.

As you can see, natively this is not supported, and this example is the only one that I was able to find, which can get you as close as possible to your desired outcome. Maybe somebody else can share different workaround, but if you would like to have it natively, then you can submit a feature request [2], to allow Straight table filter rows based on condition applied on a column.

I hope that this information was helpful!

---

[1] https://help.qlik.com/en-US/sense/August2021/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterRe....

[2] https://community.qlik.com/t5/Knowledge-Base/How-To-Submit-Feature-Requests-For-Qlik-Products/ta-p/1...

[Ragnar]

Hi Andrei,

Sorry for the delay and thank you for your above & beyond efforts in attempting to provide a solution which I think its the closet solution I can get. 

Yes you're spot on about Column functions and to help other users & visibility as fyi, these are my current column functions:

column(1)  

IF(ISNULL(max(stkh_date)),1,If(today() - max(stkh_date) < 91 ,0 ,1))

Column(2)

If(standard_cost=0, 0, 1)]

Column(3)

If(SUM({$<IsInLast18Months2={'Y'}>} stksale_movement_qty)=0,1,0)

I will provide a update either tomorrow/thursday as to how I've progressed & if I encounter some errors whilst incorporating the functions into your expression. Just working on urgent qlik tasks hence the delay.

Thanks again for your efforts. Wasn't expecting a informative response and will be in touch.

[Ragnar]

Hi, I've marked the solution as it is helpful & answered the problem I had. The user requirements has changed now.

Apologies for the delay response to this post.

Once again your support on this matter is appreciated, thank you.