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 · Accepted solution]

Something like this:

=num((sum({<DIM1 -= {'Other', 'UNKOWN'}, [Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field2])

+sum({<DIM1 -= {'Other', 'UNKOWN'}, [Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field3])

+sum({<DIM1 -= {'Other', 'UNKOWN'}, [Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field4)

+sum({<DIM1 -= {'Other', 'UNKOWN'}, [Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field5])

+sum({<DIM1 -= {'Other', 'UNKOWN'}, [Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field6])

-sum({<DIM1 -= {'Other', 'UNKOWN'}, [Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field7])

+sum({<DIM1 -= {'Other', 'UNKOWN'}, [Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field8])

)/1000000 , '$#,##0.00 M')

[MK9885]

I tried this too but not working.

[sunny_talwar]

When you say not working, does it mean it doesn't give you the right number or does it gives out an error? Please provide more information

[MK9885]

Doesn't give the right number.

The total from Pie chart not matching the total from Text box

[sunny_talwar]

Did the value change after you used the above mentioned set analysis or did it stay the same? I think you might be missing a set analysis somewhere, cause I think it should work with the above set analysis.

[Anil_Babu_Samineni]

Strange, By creating Pie chart.

Just Use Dimension as DIM1

Expression use this (Provide by sunny - Edited with ] in red color)

=num((sum({<DIM1 -= {'Other', 'UNKOWN'}, [Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field2])

+sum({<DIM1 -= {'Other', 'UNKOWN'}, [Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field3])

+sum({<DIM1 -= {'Other', 'UNKOWN'}, [Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field4])

+sum({<DIM1 -= {'Other', 'UNKOWN'}, [Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field5])

+sum({<DIM1 -= {'Other', 'UNKOWN'}, [Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field6])

-sum({<DIM1 -= {'Other', 'UNKOWN'}, [Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field7])

+sum({<DIM1 -= {'Other', 'UNKOWN'}, [Field1]={0}, [Year] = {">=$(=Min([Year List])) <=$(=Max([Year List]))"}>}[Field8])

)/1000000 , '$#,##0.00 M')

[MK9885]

Well, the mistake was from my side, I was simply copied {<DIM1 -= {'Other', 'UNKOWN'}>}

I changed the DIM1 to my dimension but didn't change the values for that dimension, hence it was not matching.

Now the total's are matching for Pie and Text. Thanks Sunny.

[MK9885]

I cannot use as just DIM1, I have to do calculated Dimension, anyhow with the Sunny's expression I got it right.

Thanks.

[MarcoWedel]

Hi,

maybe also possible:

=Num((Sum({<DIM1-={'Other','UNKOWN'}, Field1={0}, Year={">=$(=Min([Year List]))<=$(=Max([Year List]))"}>} RangeSum(Field2,Field3,Field4,Field5,Field6,Field7,Field8)))/1000000, '$#,##0.00 M')

hope this helps

regards

Marco

[MK9885]

I've used the above expression and it did change the total but it didn't exactly match the total from pie chart. However it is always good to know the other way around to write an expression.

Thanks for the help.