Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Null value in set analysis

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.

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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

NULL handling in QlikView

-Rob

View solution in original post

12 Replies
Highlighted
Master II
Master II

Try this:

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

Highlighted
Contributor III
Contributor III

Not Working...

Highlighted

May be using this

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor III
Contributor III

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

Highlighted

Would you share sample data?

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor III
Contributor III

Top BUSales
SEC625000
-1000000
EM164000
-262400
-1000000
-55200
-250000
API115000
-138000
API291666.66
-350000
API0.83
-1

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

Highlighted
MVP
MVP

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)

Highlighted
MVP
MVP

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

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

Highlighted

For me this is working

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

Capture.PNG

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)