Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

NULL Value

Hello All

Please help me in the below query. I do not know if it is possible or not in Qlik Sense

Please suggest.

In the below attached screen shot,for Bar graph, I have NULL value in the data. I want to use the formula which tells that when there is value it shows the amount and when there is no data it shows the comment that "Data is not available". Just like the Pie-Chart takes default comment.

Formula I used:

IF(len(trim(Department)) > 0, Sum(Amount))

Please suggest.

Thanks

DC

1 Solution

Accepted Solutions
vinieme12
Champion III
Champion III

Add Calculation condition and Your custom message as below

custommessage.JPG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.

View solution in original post

12 Replies
vinieme12
Champion III
Champion III

what happens when you remove the condition and do just SUM(Amount)?

try this : SUM({<Department = {*}>} Amount)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Thanks sir!!

In screenshot visualization, "Bar Graph with out formula" contain only "SUM(Amount)" formula

The above formula which you have given does not work, I am getting the same by un-check the NULL values.

Is there any formula which remove the NULL value and shows the comment

Thanks

DC

Chanty4u
MVP
MVP

try this

NULLASVALUE Field;

SET NullValue = '- ';

Anonymous
Not applicable
Author

Try using the Data Handling option(under Add-ons) in the properties panel, this allows you set a calculation condition and a display message to accompany it.

See attached example

Hope this helps

Anonymous
Not applicable
Author

Yes sir

This is what I want.

But the issue is if I use the above logic it shows the comment for all the Clients

In my dataset there is only some client where the data is null. Please suggest

Anonymous
Not applicable
Author

Can you share a sample of your dataset?

Anonymous
Not applicable
Author

Thanks for helping sir!!

Please find the below dummy script of data

Department:

LOAD * INLINE

[

Txtype,Amount,Dates,Client

A,773,42586,DN

A,812,42586,DN

A,649,42586,DN

A,968,42586,DN

A,933,42586,DN

A,804,42586,DN

P,791,42586,DN

P,677,42586,DN

P,763,42586,DN

P,876,42586,DN

P,765,42586,DN

];

Concatenate

LOAD * INLINE

[

Txtype,Amount,Dates,Department,Client

A,773,42586,E,CI

A,812,42586,E,CI

A,649,42586,E,CI

A,968,42586,P,CI

A,933,42586,P,CI

A,804,42586,S,CI

P,791,42586,S,CI

P,677,42586,T,CI

P,763,42586,T,CI

P,876,42586,M,CI

P,765,42586,M,CI

];

For DI we have no department, I want if we select DI it shows the comment in place of blank line shown in the snapshot

But for CI it will show the data. Please suggest !

Thanks

DC

vinieme12
Champion III
Champion III

Is this what you are trying to do??

Just add a a calculated field

=if(len(Department)<1,'NA',Department)

Pie_Nulls.JPG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable
Author

Thanks sir!!


I do not want to show the #n/a in the bar graph.


I want if the person select the Filter "DI", the bar graph shows the comment that "No department for the particular filter"


Just like in Pie-Chart, if there is no data it will show us the comment


Null value.png