Suppressing Null Values in Text Box?

[MK9885]

Hi,

I'd like to know if there is a way to suppress a null value in text box?

I have 4 different set analysis expressions, I'm adding all those expressions to get total.

I've tried using 'Not IsNull' but doesn't work.

Below is my expression:

=num((sum({<[Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field2])
+sum({<[Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field3])
+sum({<[Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field4)
+sum({<[Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field5])
+sum({<[Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field6])
-sum({<[Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field7])
+sum({<[Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field8])
)/1000000 , '$#,##0.00 M') 

stalwar1

Thanks.

[sunny_talwar]

What do you want o see when it is null? Or am I not understanding your requirement correctly?

[Anil_Babu_Samineni]

Can you share the Screen, How image looks? Better approach if you would provide sample

[MK9885]

So with the above expression in a pie chart (after adding all the values) I'm getting a total, ex: $ 250 M (after suppressing Null)

While in my text box I'm getting total of $ 290M (cus the null values are not suppressed here)

If I uncheck the supress null value in Pie chart, the total is matching with the text box total.

[sunny_talwar]

What is your pie chart dimension here? You need to add that to your set analysis in each of your aggregating function above:

{<DimensionName = {'*'}>}

[MK9885]

Sorry, I cannot provide the sample, the file is too large to upload here.

It is just a Text box showing total, I.E Sum of all those values.

I'm adding all the values to match the total to show in text box.

Pie Chart

Value1

Value2

Value3

Value4

Value5

Total: $250 M (after supressing Null)

Text box

Same expression used as above but want to supress null value to match the Total: $250M

[MK9885]

I tried that, it's not working.

Cus in dimension I'm using calculated dimension, not a direct field.

[sunny_talwar]

What is your calculated dimension here?

[MK9885]

I'm not using the calculated dim in the above expressions.

But it is

=if( ([DIM1]<>'Other' and [DIM1]<>'UNKNOWN'),[DIM1])

[sunny_talwar]

Try this as your set analysis

{<DIM1 -= {'Other', 'UNKOWN'}>}