topic Re: Remove Null values in Text box calculation in QlikView

Remove Null values in Text box calculation

amit_saini — Thu, 22 Sep 2016 09:22:08 GMT

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,

Re: Remove Null values in Text box calculation

sunny_talwar — Thu, 22 Sep 2016 09:28:18 GMT

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

avinashelite — Thu, 22 Sep 2016 09:34:58 GMT

may be this

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

Re: Remove Null values in Text box calculation

sunny_talwar — Thu, 22 Sep 2016 09:37:21 GMT

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

amit_saini — Thu, 22 Sep 2016 09:49:46 GMT

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

sunny_talwar — Thu, 22 Sep 2016 09:51:12 GMT

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

Re: Remove Null values in Text box calculation

avinashelite — Thu, 22 Sep 2016 09:57:20 GMT

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

Re: Remove Null values in Text box calculation

amit_saini — Thu, 22 Sep 2016 10:28:37 GMT

Yes Bro!