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

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

Tue, 14 Dec 2010 13:08:54 GMT

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.

Thanks, in advance, for your time.

Regards

Rob

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

marcel_olmo — Tue, 14 Dec 2010 13:38:09 GMT

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!!

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

maneshkhottcpl — Tue, 14 Dec 2010 14:04:12 GMT

Can u send sample application?

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

Tue, 14 Dec 2010 14:46:23 GMT

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.

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

Tue, 14 Dec 2010 14:56:13 GMT

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?

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

Tue, 14 Dec 2010 15:29:09 GMT

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?

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

Tue, 14 Dec 2010 15:51:06 GMT

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