Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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
Contributor III

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

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
Contributor III

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

hi

Can u send sample application?

Not applicable

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

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

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

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

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

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

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

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