Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
AWS Degraded - You may experience Community slowness, timeouts, or trouble accessing: LATEST HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Filter based on a column

Hi, the attachment is an example of this data:

NameValue_AValue_BA - B(A - B) * Factor
Aaron. STEVENS109228-119-178
Abraham. CARROLL181229-48-72
Abraham. JAMES181213-32-48
Abraham. JONES18212260150
Abraham. RAY199233-34-51
Achille. GRANT1261091734
Achille. RICE114247-133-199

The fourth column is the difference between Value_A - Value_B, and I called 'A - B'.

The fifth column is 'A - B' multiplied by a factor, these are the rules:

     A-B <= 0 then factor = 1.5

     A-B <= 50 then factor = 2.0

     A-B <= 70 then factor = 2.5

     A-B greater that 70 then factor = 3.0

The problem is this:

I need to filter the table using the last columna (A-B * Factor), I like to have one filter where show:

  • Only the NEGATIVE values.
  • Without duplicates.

Like this:

FILTER: (A - B) * Factor
-220
-214
-211
-210
-207

Then, the data is filtered according to the negative value selected in the filter.

Thanks.

5 Replies
rbecher
MVP
MVP

Hi Javier,

you could use a listbox with this expression:

=if(Difference<0, 'Difference<0')

- Ralf

Data & AI Engineer at Orionbelt.ai - a GenAI Semantic Layer Venture, Inventor of Astrato Engine
swuehl
MVP
MVP

Yes, Difference is determining the outcome of the table, right?

In general, you can take care that all columns return zero / NULL for the rows to hide, then use 'Suppress zero values' in presentation tab to hide.

Not applicable
Author

I need to make some clarifications:

1. I make a filter: =If((Value_A - Value_B) <= 0,  (Value_A - Value_B) * 1.5)

2. When I select a value in the filter, example -40.50, all ok and only show the values where '(A - B) * Factor' = -40.50

3. But, when I select -12.00 the values in '(A - B) * Factor' are varied: -12.00, 34.00, -109.50, etc

Why don´t work if I select only a value?

Please look attachment

Thanks.

rbecher
MVP
MVP

Hi Javier,

it is always better to have a real field for a filter, please find attached example.

- Ralf

Data & AI Engineer at Orionbelt.ai - a GenAI Semantic Layer Venture, Inventor of Astrato Engine
kedar_dandekar
Creator
Creator

Hi Javier,

I was trying to figure out a possible explanation, and here is what I think happens.

The actual data fields used in the model are Name, Value_A and Value_B, the rest of the columns are calculated i.e. A-B and (A-B) * Factor.

When we filter data based on the calculated value, QlikView internally uses the underlying fields for filtering the data (in this case  Value_A and Value_B), so when you select -12.00, effectively the data is filtered for the selected values of Value_A and Value_B instead of for the calculated value of -12.00.

e.g. Karelle. WHITE has both Value_A (121) and Value_B (194) that match with the ones selected for -12.00 (i.e. selected values in the Yellow highlighted listbox), hence this row too gets filtered though its (A-B)* Factor value is -109.50.

DataFiltering2.png

So to conclude, as mentioned by Ralf, it is always better to have a real field for a filter

HTH,

KD