topic Re: Null value in set analysis in QlikView

Null value in set analysis

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

I would like to know the sales values when Top BU is null. I tried the below expression but it is not working.

sum({<[Top BU]={"$(=IsNull([Top BU]))"}>}Sales).

I would like to have your suggestions to correct my expression.

Re: Null value in set analysis

sushil353 — Tue, 24 Jan 2017 06:53:23 GMT

Try this:

sum({<[Top BU]={""}>}Sales)

Re: Null value in set analysis

Anonymous — Tue, 24 Jan 2017 06:55:41 GMT

Not Working...

Re: Null value in set analysis

Anil_Babu_Samineni — Tue, 24 Jan 2017 06:59:09 GMT

May be using this

=Sum({$-<[Top BU]={'*'}>} Sales)

Re: Null value in set analysis

Anonymous — Tue, 24 Jan 2017 07:03:44 GMT

I think above will give sales where top bu is not null. But I am looking for Sales when Top BU is NULL.

Re: Null value in set analysis

Anil_Babu_Samineni — Tue, 24 Jan 2017 07:05:20 GMT

Would you share sample data?

Re: Null value in set analysis

Anonymous — Tue, 24 Jan 2017 07:14:21 GMT

Top BU	Sales
SEC	625000
-	1000000
EM	164000
-	262400
-	1000000
-	55200
-	250000
API	115000
-	138000
API	291666.66
-	350000
API	0.83
-	1

When I exported, null values are replaced with '-'.

Re: Null value in set analysis

MK_QSL — Tue, 24 Jan 2017 07:23:19 GMT

Data:

Load

If(IsNull([Top BU]) or Len(Trim(Replace([Top BU],'-','')))=0, Null(), [Top BU]) as [Top BU],

If(IsNull([Top BU]) or Len(Trim(Replace([Top BU],'-','')))=0, 1,0) as TopBUNullFlag,

Sales

Inline

[

Top BU, Sales

SEC, 625000

-, 1000000

EM, 164000

-, 262400

-, 1000000

-, 55200

-, 250000

API, 115000

-, 138000

API, 291666.66

-, 350000

API, 0.83

-, 1

];

Now use as below

=SUM({<TopBUNullFlag = {1}>}Sales)

Re: Null value in set analysis

MK_QSL — Tue, 24 Jan 2017 07:25:20 GMT

If you don't want to change anything in script then use as below..

=SUM({1-<[Top BU] = {'*'}>}Sales)

Re: Null value in set analysis

Anil_Babu_Samineni — Tue, 24 Jan 2017 07:25:40 GMT

For me this is working

Sum({<Top -= {"-"}>}[BU Sales]) // - and = is there

Re: Null value in set analysis

rwunderlich — Tue, 24 Jan 2017 07:34:06 GMT

I think Anil's answer is correct. Note the "-" after $. See also

NULL handling in QlikView

-Rob

Re: Null value in set analysis

Anonymous — Tue, 24 Jan 2017 07:47:18 GMT

Try this

sum({<[Top BU] ={'=$(=len([Top BU])=0)'}>}Sales)

Re: Null value in set analysis

Anonymous — Wed, 25 Jan 2017 05:16:38 GMT

I think suggestions from AMnish Kachhia, rgv rand , Anil Babu should be worked.

But they did not work for me, not sure maybe because of the model I have.

Good article to know about the 'NULL'.

NULL handling in QlikView

However, I solved my problem by adding the below calculated dimension without changing anything in the expression.

if(IsNull([Top BU]),'Yes'), and checked the option "Suppress when value is NULL"

Re: Null value in set analysis

Gaël — Fri, 27 Jun 2025 13:16:38 GMT

I hope this follow-up will be useful for other readers.

Having a similar problem, I noticed that
the complement to Sum({$<[Top BU] = {"*"}, ...>}Sales)
is not Sum({$<[Top BU] -= {"*"}, ...>}Sales) as I have read in this forum somewhere.

Your solution is Sum({$-<[Top BU] ={"*"}>}Sales). This already helped me. Thanks! Alternatively, the less elegant expression
Sum({$<ID=E({<[Top BU]={"*"}>} ID), ...>} Sales) works as well.

To combine this with my extra conditions, I wrote Sum({$<...>-<[Top BU] ={"*"}>}Sales).