Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
)))
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;
Hi,
Can you attach some sample file?
Regards,
Jagan.
Attach a Sample file
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
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
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
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 ?
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)
)))
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