topic Re: Re: Filter based on a column in QlikView

Filter based on a column

Mon, 02 Sep 2013 16:12:01 GMT

Hi, the attachment is an example of this data:

Name	Value_A	Value_B	A - B	*(A - B) Factor**
Aaron. STEVENS	109	228	-119	-178
Abraham. CARROLL	181	229	-48	-72
Abraham. JAMES	181	213	-32	-48
Abraham. JONES	182	122	60	150
Abraham. RAY	199	233	-34	-51
Achille. GRANT	126	109	17	34
Achille. RICE	114	247	-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.

Re: Filter based on a column

rbecher — Mon, 02 Sep 2013 16:21:16 GMT

Hi Javier,

you could use a listbox with this expression:

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

- Ralf

Re: Filter based on a column

swuehl — Mon, 02 Sep 2013 16:27:19 GMT

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.

Re: Filter based on a column

Mon, 02 Sep 2013 18:41:37 GMT

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.

Re: Re: Filter based on a column

rbecher — Mon, 02 Sep 2013 18:48:02 GMT

Hi Javier,

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

- Ralf

Re: Filter based on a column

kedar_dandekar — Mon, 02 Sep 2013 22:53:21 GMT

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.

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

HTH,