topic Re: Table cannot be filtered based on null values in App Development

Table cannot be filtered based on null values

Qlik_Stek — Sun, 13 Oct 2024 15:22:50 GMT

Hey there,

I have a table in my app that contains a few dimensions and 3 measures.

The 3 measures are:

1. Current Period - Represents the amount of money I currently have (based on period selection)

2. Compared Period - Represents the amount of money I had in a selected period (also based on period selection, using a different variable than the one used in current period)

3. Difference - the calculation of money in my current period minus the money in the compared period

Also, I have a variable that gets a number that is used as a threshold for amounts of money (we'll call it $Threshhold).

I'm trying to filter rows from the table based on the next condition:

If the absolute value of the difference between the periods is greater than $Threshold, then keep the value of it in the table, otherwise filter it.

The problem is that I can't find a way to actually filter the values from the table, the become a null value instead.

I tried many IF conditions and also tried using the limitation option in one of the dimensions, but to no avail.

I would really appreciate if someone could help me with this.

(And sorry I cant ease it with real examples).

Thanks!

Re: Table cannot be filtered based on null values

delmak2000 — Mon, 14 Oct 2024 16:06:46 GMT

Hi @Qlik_Stek,

It would be great to share some data examples to shed more light on the question above. You can make up fictitious data to help everyone understand the challenge.

Have you tried using set analysis to determine the conditions listed above rather with the if conditions? Something like:
If(Aggr(
Sum({< Difference = {">$($Threshhold)"} Value), Dim > 0,
Value, Null()
)

Regards

Re: Table cannot be filtered based on null values

Scotchy — Mon, 14 Oct 2024 22:25:44 GMT

Try this approach...

To filter rows in your table based on the condition where the absolute value of the difference between the periods exceeds $Threshold, you can use a calculated dimension along with the option to suppress null values. Here's how you can achieve this:

Create a Calculated Dimension: Replace your existing dimension with a calculated dimension that checks your condition. For example, if your dimension is Product, you can create the following calculated dimension:

=If(Abs([Current Period] - [Compared Period]) > $Threshold, [Product])

This expression will return the Product name if the condition is met; otherwise, it returns NULL.
Suppress Null Values: In the table properties, navigate to the dimension you've just created. Enable the option "Suppress When Value Is Null". This setting will remove any rows where the dimension value is NULL, effectively filtering out rows that don't meet your condition.
Ensure Measures Align with the Filter: Make sure your measures (Current Period, Compared Period, and Difference) are calculated in a way that aligns with the filtered data. Since the dimension now only includes rows meeting the condition, the measures will automatically reflect this subset of data.

Alternative Approach Using Set Analysis:

If you prefer to filter data within your measures, you can use set analysis expressions. However, note that this method filters data at the measure level and doesn't remove entire rows from the table.

For example:

Sum({<YourField = {"=Abs([Current Period] - [Compared Period]) > $Threshold"}>} [YourMeasure])

But for your requirement—to remove entire rows—the calculated dimension method with suppressed nulls is the most effective.

Summary:

Calculated Dimension: Filters out rows based on your condition.
Suppress Nulls: Removes rows where the calculated dimension is NULL.
Result: Your table displays only the rows where the absolute difference exceeds $Threshold.

Example:

Suppose your dimensions are Customer and your measures are as described. Your calculated dimension would be:

=If(Abs([Current Period] - [Compared Period]) > $Threshold, [Customer])

Enable "Suppress When Value Is Null" for this dimension, and your table will now only display customers where the condition is met.

Re: Table cannot be filtered based on null values

Qlik_Stek — Mon, 21 Oct 2024 13:39:53 GMT

Hey @Scotchy @delmak2000 ,

I've tries your ways and unfortunately it didn't work for me.

Could it be because all the measures I've detailed are based on variables?

Also, I tried to manipulate the Null values I'm getting in the 'Difference' column, but no matter how I try to use that, it doesn't do anything, not calculations and no conditions.

Do you happen to know why is that?

Thank you both!