7 Replies Latest reply: Sep 22, 2016 6:28 AM by Amit Saini

# Remove Null values in Text box calculation

Hi Folks ,

I'm having below expression in text box:

='€' & Num(\$(eSALES_ACTUAL) / 1000000, '####') &' M'

I want to show in calculation when Plant >0 , I want to avoid numbers for null values of Plant.

Thanks,

AS

• ###### Re: Remove Null values in Text box calculation

Amit how do you define eSALES_ACTUAL variable? Can you change its definition? If not, then may be try this:

Sum(Aggr(If(Len(Trim(Plant)) > 0, Num(\$(eSALES_ACTUAL)/1000000, '#### M'), Plant))

I also put the euro sign and mill within the number format to make things easier.

UPDATE: Change my logic for using Len(Trim(Plant)) > 0 based on Avinash's response below. But in case we are checking for Plant > 0, then may be this:

Sum(Aggr(If(Plant > 0, Num(\$(eSALES_ACTUAL)/1000000, '#### M'), Plant))

• ###### Re: Remove Null values in Text box calculation

may be this

if(len(trim(Plant))>0,€' & Num(\$(eSALES_ACTUAL) / 1000000, '####') &' M'')

• ###### Re: Remove Null values in Text box calculation

Avinash -

Text boxes don't have any dimensions, how will Len(Trim(Plan)) > 0 will be evaluated outside of any aggregation. This statement will end up being false and will show null, isn't it?

• ###### Re: Remove Null values in Text box calculation

stalwar1 sorry Bro , I forgot that we are having this issue in text box .

• ###### Re: Remove Null values in Text box calculation

I got it

='€' & Num(sum({<[Reporting Unit_Name] -= {'-'}>}ACTUAL_SALES)/1000000, '####') &' M'

This is working.

Thanks
AS

• ###### Re: Remove Null values in Text box calculation

So it was not even null? It was infact -?

• ###### Re: Remove Null values in Text box calculation

Yes Bro!