Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to filter data based on expression value?

Hi,

Here is my straight table structure.

ClientBal SheetRevROA
ABC12222016
CDE98993000303

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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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)

)))

View solution in original post

12 Replies
SergeyMak
Partner Ambassador
Partner Ambassador

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;

Regards,
Sergey
jagan
Luminary Alumni
Luminary Alumni

Hi,

Can you attach some sample file?

Regards,

Jagan.

Anonymous
Not applicable
Author

Attach a Sample file

Not applicable
Author

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

Not applicable
Author

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
Master
Master

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

Not applicable
Author

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
MVP
MVP

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)

)))

Not applicable
Author

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