topic Re: Suppressing Null Values in Text Box? in QlikView

Suppressing Null Values in Text Box?

MK9885 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Suppressing Null Values in Text Box?

sunny_talwar — Mon, 07 Nov 2016 16:35:32 GMT

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

Re: Suppressing Null Values in Text Box?

Anil_Babu_Samineni — Mon, 07 Nov 2016 16:36:24 GMT

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

Re: Suppressing Null Values in Text Box?

MK9885 — Mon, 07 Nov 2016 16:41:15 GMT

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.

Re: Suppressing Null Values in Text Box?

sunny_talwar — Mon, 07 Nov 2016 16:42:53 GMT

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

{<DimensionName = {'*'}>}

Re: Suppressing Null Values in Text Box?

MK9885 — Mon, 07 Nov 2016 16:43:36 GMT

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

Re: Suppressing Null Values in Text Box?

MK9885 — Mon, 07 Nov 2016 16:47:38 GMT

I tried that, it's not working.

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

Re: Suppressing Null Values in Text Box?

sunny_talwar — Mon, 07 Nov 2016 16:48:25 GMT

What is your calculated dimension here?

Re: Suppressing Null Values in Text Box?

MK9885 — Mon, 07 Nov 2016 16:50:53 GMT

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

But it is

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

Re: Suppressing Null Values in Text Box?

sunny_talwar — Mon, 07 Nov 2016 16:52:52 GMT

Try this as your set analysis

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

Re: Suppressing Null Values in Text Box?

sunny_talwar — Mon, 07 Nov 2016 16:55:09 GMT

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

Re: Suppressing Null Values in Text Box?

MK9885 — Mon, 07 Nov 2016 16:58:28 GMT

I tried this too but not working.

Re: Suppressing Null Values in Text Box?

sunny_talwar — Mon, 07 Nov 2016 17:01:04 GMT

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

Re: Suppressing Null Values in Text Box?

MK9885 — Mon, 07 Nov 2016 17:02:25 GMT

Doesn't give the right number.

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

Re: Suppressing Null Values in Text Box?

sunny_talwar — Mon, 07 Nov 2016 17:06:21 GMT

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.

Re: Suppressing Null Values in Text Box?

Anil_Babu_Samineni — Mon, 07 Nov 2016 17:11:21 GMT

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

Re: Suppressing Null Values in Text Box?

MK9885 — Mon, 07 Nov 2016 18:07:03 GMT

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.

Re: Suppressing Null Values in Text Box?

MK9885 — Mon, 07 Nov 2016 18:08:06 GMT

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

Thanks.

Re: Suppressing Null Values in Text Box?

MarcoWedel — Mon, 07 Nov 2016 21:14:35 GMT

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

Re: Suppressing Null Values in Text Box?

MK9885 — Tue, 08 Nov 2016 15:46:02 GMT

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.