How to filter data based on expression value?

[#1]

Hi,

Here is my straight table structure.

Client	Bal Sheet	Rev	ROA
ABC	1222	20	16
CDE	9899	3000	303

Basically I have Client as the dimension and Bal Sheet, Rev and ROA as expressions

Interesting part is ROA expression is calculated using Bal Sheet and Rev

ROA = (Rev / bal Sheet ) * 1000

Now I wanted to have a filter to show ROA < 50 , ROA between 50 to 100 , ROA > 100

I tried to do a data island with list box filter. On selecting the ROA filter list box (let say I choose < 50) I see ROA column displays values < 50 but it also contains other rows which are not < 50 (displayed as nulls).

I basically only want to display those rows matching the ROA filter.

Hope I am clear.

Please let me know how to achieve this ?

Thanks

[#2]

I am new to Qlikview. Can some one please explain how Massimo Grossi's solution works ?

Thanks

[#3]

Hi dimple.

If(Rev / [Bal Sheet] * 1000 >100, dual('>100',100) <- this expression checks if rev / bal * 1000 is greater than 100. Dual returns two values: a text value (in this case '>100' and a number value (in this case 100). So selecting >100 in the list box filters to values that satisfy Rev / Bal Sheet * 1000 > 100 ( in this case  CCC and CDE).

The rest of the function works in a similar way.

[Anonymous]

//this technique of stacking load statements is called preceeding loads. Search for HIC + Preceeding loads in the community

LOAD *,

     Class(ROA ,50 ,'ROA ' ) as ROA //replace the 50  with any increment you want. Class allows you to split any figure into classes. Very useful. Could be combined with aggregate functions.

;

LOAD *,

( (Rev / bal Sheet ) * 1000 ) as ROA ; //Calculate the item price. in the preceeding load above, you can reuse ItemPrice and QlikView will understand its content. Very powerful load technique

LOAD * INLINE [

Client, bal Sheet, Rev,

ABC, 1222, 20,

CDE, 9899, 3000,

]

;

Hope this helps...