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: 
Not applicable

How do I exclude or supress null data from an expression?

Hi all,

I hope you can help.

I am trying to calculate Service Downtime from recorded incidents against set SLAs. Where an incident has no downtime we have no data recorded which when we come to reporting on it gives us null results

This is my original expression.

((((varMaxDateNum-varMinDateNum)+1)-ServiceDowntime)/((varMaxDateNum-varMinDateNum)+1))



However, it presented null results and these confused other calculations based from the product of this expression.

I have tried this but I still get the Null results showing up.

=if(not isnull(ServiceDowntime),((((varMaxDateNum-varMinDateNum)+1)-ServiceDowntime)/((varMaxDateNum-varMinDateNum)+1)),'1')

What I want to achieve is the Null result to either not appear or return a value of 100% in the chart.

error loading image

Thanks, in advance, for your time.

Regards

Rob

6 Replies
marcel_olmo
Partner Ambassador
Partner Ambassador

Hey Rob, for null values in set analysis is something like this (untested) :

Normal 0 21 false false false ES X-NONE X-NONE MicrosoftInternetExplorer4

=sum({$-<ServiceDowntime= -{} >}ServiceDowntime)

I'm not pretty sure about what you want, but the point is to do " -{} ".

Hope it helps!!

maneshkhottcpl
Partner - Creator III
Partner - Creator III

hi

Can u send sample application?

Not applicable
Author

Marcel - I'll give it a try and let you know.

Manesh - not easily there's a lot of sensetive data involved in the search criteria. I'll see if I can strip it down to a 'working' variant with no client data loaded.

Not applicable
Author

As a thought is there some way in the script I could replace the null value with a zero without affecting the other values for that field?

Not applicable
Author

You can replace the nulls, but it uses a similar syntax to your expression that isn't working. Looking over the expression, it probably should work. Did you replace the Null symbol in your chart with Null?

In your load you would use:

If(IsNull(ServiceDowntime), 0, ServiceDowntime) As ServiceDowntime
My worry is that if the logic isn't working in an expression, it may not work in your load either.

Did you have any luck with Marcel's expression?

Not applicable
Author

Just go to, Presentation > select service availability > mark orbit rows where field is null?