Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Applicable88
Creator III
Creator III

To sum up Value only those where its Null

Hello,

I have two tables associated via Datamanager.

I want to sum up a value with following condition:

sum({1<Month1={"$(=Num(Month(Today())))"},Year1={"$(=year(Today()))"}>}[Sales])

I want to add one more condition: only where its Null()

So I tried this:

sum({1<Month1={"$(=Num(Month(Today())))"},Year1={"$(=year(Today()))"},ProfitNO={"Null()"}>}[Sales])

or this:

sum({1<Month1={"$(=Num(Month(Today())))"},Year1={"$(=year(Today()))"},isNull(ProfitNO)}>}[Sales])

I also try to give those fields where its null a new name in the load script so I can make simpler set expression:

is(isnull(ProfitNO), 'NEWNAME', ProfitNO)

But it still shows me all value as  "-" 

I hope someone has an idea.

Thank you very much in advance.

Best. 

 

1 Solution

Accepted Solutions
Applicable88
Creator III
Creator III
Author

Hello @MayilVahanan , @dplr-rn ,

thank you very so far. I tried both not working.

But after I used "nullcount()=0" in a set expression it works out. Can you explain why len(trim)) and isNull not returning the same? 

Thank you . 

Best.

 

View solution in original post

3 Replies
dplr-rn
Partner - Master III
Partner - Master III

instead of isnull try len(ProfitNO)=0

MayilVahanan

HI @Applicable88 

Try like below

If(isnull(ProfitNO) or Len(Trim(ProfitNO))=0, 'NEWNAME', ProfitNO) as FieldName

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Applicable88
Creator III
Creator III
Author

Hello @MayilVahanan , @dplr-rn ,

thank you very so far. I tried both not working.

But after I used "nullcount()=0" in a set expression it works out. Can you explain why len(trim)) and isNull not returning the same? 

Thank you . 

Best.