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

[SergeyMak]

You can move this logic to the load script

something like

[tmpgrp]:

LOAD ClientId, Month

IF(SUM(Rev)/SUM([Bal Sheet])*1000<50,'<50',

     IF(SUM(Rev)/SUM([Bal Sheet])*1000>=100, '>100', '>=50<100') AS Filter

Resident your_data_table

GROUP BY ClientId, Month;

LEFT JOIN (your_data_table)

LOAD *

Resident tmpgrp;

DROP Table tmpgrp;

[jagan]

Hi,

Can you attach some sample file?

Regards,

Jagan.

[Anonymous]

Attach a Sample file

[#5]

Sergey, ROA has to be calculated on the QV front end.

I currently have the Load script resolution but the issue is we have multiple dimensions.

So when slicing the data -  it shows wrong results

So its better to do from the front end side

[#6]

hello Jagan and Nitin,

I don't know how to provide a sample file. Our QVW is huge with so many dimensions/slicers, big data model.

But basically I wanted to filter the expressions.

Thanks

[sasikanth]

Hi Dimple

Your BalSheet and Rev are also caluculated Exp ? if then provide the calculation part

if not then

Use in listbox expression

Let var=(rev/Balsheet)*1000 

if($(var)<50, 'ROA<50',

if($(var)>50 and $(var)<100, 'ROA 50 to 100',

if( $(var)>100,'ROA>100')))

Hope this will help you

[#8]

Hi Sasikanth

I think you are trying to move the same logic from load script to list box. The problem is there are many dimension fields and if we statically calculate ROA - then its a problem

What I did is introduce new columns based on ROA value

< 50 , > 100 , 50-100 columsn

The problem is it displays the values correctly in the buckets.

how can I hide the null rows  now ?

[maxgro · Accepted solution]

maybe (see attachement)

=(

if(Rev / [Bal Sheet] * 1000 >100, dual('>100',100),

if(Rev / [Bal Sheet] * 1000 >50, dual('50...100',50),

dual('0...50',0)

)))

[#10]

This looks promising. Can you please explain how this works?

Basically I am not able to understand how the ROA field is getting affected when you change the list box?

Thanks