Remove Null values in Text box calculation

[amit_saini]

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.

Please suggest!

Thanks,

AS

[sunny_talwar]

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

[avinashelite]

may be this

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

[sunny_talwar]

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?

[amit_saini · Accepted solution]

I got it

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

This is working.

Thanks
AS

[sunny_talwar]

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

[avinashelite]

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

[amit_saini]

Yes Bro!