Hiding Rows Based On Blank Fields

[pnn44794]

Hello:

I suspect I'm making this more difficult than it needs to be, but I'm stuck at the moment.  I'm using a straight table with 23 columns.  I want to suppress / hide rows where estimate_is_active is blank or otherwise not equal to True and where project_is_active is blank or otherwise not equal to True.  So, two conditions and it might be easier to understand my ask this way:

if estimate_is_active = 'True' AND project_is_active = 'True' Then Display the Row, Else Do NOT display the Row

I do have several thousand rows with True for both estimate_is_active and project_is_active as well as rows where both are blank.

As always, thanks in advance for any and all help.

[devarasu07]

Hi,

Try like below (in your dimension key field try to use like below expression)

=if(aggr((Len(estimate_is_active)>0 or estimate_is_active = 'TRUE') and project_is_active = 'TRUE', ID),ID)

or in your expression using set analysis

Sum({<estimate_is_active={'=(Len(estimate_is_active)>0)'},project_is_active={'TRUE'},estimate_is_active={'TRUE'}>}SoldQty)

Refer to the attached sample.

Thanks,

Deva

[pnn44794]

Hello Deva:

Thank you for the response.  I tried your suggestion as an Expression and as a Calculated Dimension.  No luck.  I still get rows where estimate_is_active and project_is_active are both blank.  Should the "and project..." part also have a Len check?

[devarasu07]

Hi,

Can you share your issue with mock app, it will be easy for us to check and help you easily

Thanks,

Deva

[pnn44794]

Absolutely.  There are no other expressions or calculated fields.

[vishsaggi]

Try this in all your dimensions?

= Aggr(IF( estimate_is_active = 'True' AND project_is_active = 'True', [DM #]), [DM #])

= Aggr(IF( estimate_is_active = 'True' AND project_is_active = 'True', effective_item_qty), [DM #], effective_item_qty)

= Aggr(IF( estimate_is_active = 'True' AND project_is_active = 'True', estimate_is_active), [DM #], estimate_is_active)

= Aggr(IF( estimate_is_active = 'True' AND project_is_active = 'True', project_is_active), [DM #],project_is_active)

...... Let us know if that works?

[Anonymous]

the easiest way solving this, is using a flag:

LOAD [DM #],
effective_inf_order_date,
[Project #],
[Estimate #],
effective_item_name,
effective_item_qty,
effective_monthly_cost,
estimate_include_in_forecast,
estimate_include_in_budget,
estimate_is_active,
project_is_active,
if (estimate_is_active='TRUE' and project_is_active = 'TRUE','X') as YOURFLAG
FROM [Hide-Row-Sample-Data.xlsx] (ooxml, embedded labels, table is Sheet1);

Then you use set analysis, example:

sum({<YOURFLAG = {'X'}>} effective_monthly_cost)

[CarlosAMonroy]

Hi Perry,

Try using the following expression in your table:

=if( len(estimate_is_active) > 0 and estimate_is_active = 'True' and len(project_is_active) > 0 and project_is_active = 'True', MetricField)

Hope that helps,

Carlos M

[pnn44794]

Hello Robin:

Thanks for the response.  I tried your suggestion, but my script fails to load.  Please note and I believe this makes a difference, I'm actually loading the data from a SQL Server database using SQL Select and not an Excel file.  I provided the Excel file so people could have some sample data.

[pnn44794]

Hello Vishwarath:

Thanks for your response.  Just to make sure I understand, are you saying that all of the dimensions used should be calculated dimensions using Aggr?  I appreciate your patience as I'm still learning.  Thanks.