topic Re: How to Exclude Null Values from Calculated Expression? in QlikView

How to Exclude Null Values from Calculated Expression?

Wed, 18 Mar 2015 13:32:44 GMT

Hi All,

How do i exclude Nulls from my Calculated Dimension Expression?

Below is my Code:

=if( aggr(rank(sum(People)),Service)<=vTopValue,Service)

SAMPLE TABLE:

Service	People
A	200
B	34
-	676
-	90

OUTPUT:

Service	People
A	200
B	34

Thanks in Advance.

Re: How to Exclude Null Values from Calculated Expression?

rubenmarin — Wed, 18 Mar 2015 13:34:57 GMT

Hi Thabiso, select the dimension and check 'supress when value is null'

Re: How to Exclude Null Values from Calculated Expression?

maleksafa — Wed, 18 Mar 2015 13:35:35 GMT

under the dimension tab, select the dimension and then click on suppress when value is null

Re: How to Exclude Null Values from Calculated Expression?

sunny_talwar — Wed, 18 Mar 2015 13:35:41 GMT

Did you try using Suppress Null Values on the Dimension tab of chart properties?

HTH

Best,

Re: How to Exclude Null Values from Calculated Expression?

Wed, 18 Mar 2015 13:40:19 GMT

Hi All,

Thanks for your quick response, but i want to write it within the Code not through the Suppress Null Method, is there any way to do this? some of the Queries i used Suppress bt on this one i wanna use Code, in order to boost my skills.

Thank you.

Re: How to Exclude Null Values from Calculated Expression?

sunny_talwar — Wed, 18 Mar 2015 13:44:30 GMT

By code do you mean from the script? If yes, then you can write the following code:

LOAD SERVICE

FROM yourLocation

Where Len(Trim(SERVICE)) <> 0;

Re: How to Exclude Null Values from Calculated Expression?

luciancotea — Wed, 18 Mar 2015 13:46:11 GMT

=if( aggr(rank(sum(People)),Service)<=vTopValue and not isnull(Service),Service)

Re: How to Exclude Null Values from Calculated Expression?

sunny_talwar — Wed, 18 Mar 2015 13:47:27 GMT

I guess by code you mean the expression:

my guess is that your expression for People is Sum(People). If it is then you should be able to use the following expression for People:

=Sum({<Service = {'*?'}>}People)

Try and see if it works.

Best,

Re: How to Exclude Null Values from Calculated Expression?

Wed, 18 Mar 2015 13:47:29 GMT

Hi Sunindia,

is it possible to apply same code under Chart Properties where i added a Calculated Dimension?

thats where i want the code to be, not on the Load Script, directly from Front End?

Thanks.

Re: How to Exclude Null Values from Calculated Expression?

rubenmarin — Wed, 18 Mar 2015 13:50:54 GMT

Hi Thabiso, on your data, if expression was Sum(People), and ther were only services A and B, the chart will still show the null dimension to sum all that People not related to dimension. This is what is happening, if you achieve to modify your calculated dimension to avoid nulls, they still can be shown because of expression.

Usually the best way to avoid null dimensions is check that 'Supress when is null', if for some reason you don't want it, you'll need to change expressions, in example you can use this expression:

If(Match(Service, if( aggr(rank(sum(People)),Service)<=vTopValue,Service)),

Sum(People))

Re: How to Exclude Null Values from Calculated Expression?

Wed, 18 Mar 2015 13:52:10 GMT

Hi Lucian,

It doesn't work, basically i need it to add the next row, incase i want Top 5 Sales, then one of the Rows is Null, then jump to next Row to get direct num Top 5

Regards.

Re: How to Exclude Null Values from Calculated Expression?

Wed, 18 Mar 2015 13:54:57 GMT

Hi Ruben,

so if i suppress, then is it possible to add 1 more row so that the Top Range would be correct, because if suppressed and my request was show me Top 5 of Sales: then if one row is Supressed it will show 4 rows, so now is it possible to add one more row in case to get correct Range?

Thanks.

Re: How to Exclude Null Values from Calculated Expression?

rubenmarin — Wed, 18 Mar 2015 13:59:05 GMT

Hi Thabiso, i don't know why is showing only 4 rows, if you tell 5 it should show 5.

Probably there are People unrelated to a service, and People with null service is in top five, lowering all other services one spot, can you check if this is what is happening?

Re: How to Exclude Null Values from Calculated Expression?

Wed, 18 Mar 2015 13:59:51 GMT

Hi,

it shows Error in Calculated Dimension.

any reason why?

Thanks.

Re: How to Exclude Null Values from Calculated Expression?

rubenmarin — Wed, 18 Mar 2015 14:00:57 GMT

If is this you can change calculated dimension to:

=if(aggr(rank(sum({<Service={"*"}>} People)),Service)<=vTopValue,Service)

Re: How to Exclude Null Values from Calculated Expression?

rubenmarin — Wed, 18 Mar 2015 14:02:14 GMT

Hi, what I posted in that post was for expression, not dimension.

Re: How to Exclude Null Values from Calculated Expression?

Wed, 18 Mar 2015 14:02:17 GMT

Hi,

it only shows 4 if i suppressed, but if i didin't then it shows 5, but the 5th one is null field but with the value on the other column and its basically high on them all.

so there's no other solution to exclude null and count the next row?

Re: How to Exclude Null Values from Calculated Expression?

Wed, 18 Mar 2015 14:06:41 GMT

Hi,

on the Expression i tried, and it works but still my top range is 5, it shows me 4, so i want it to add one more row so that it becomes 5 rows.

Thanks.

Re: How to Exclude Null Values from Calculated Expression?

Wed, 18 Mar 2015 14:07:09 GMT

Hi Thabiso

try this as the calculated dimension

If( Service <> null(), if( aggr(rank(sum(People)),Service)<=vTopValue,Service) )

/Teis

Re: How to Exclude Null Values from Calculated Expression?

rubenmarin — Wed, 18 Mar 2015 14:11:57 GMT

Hi, i crated this data:

Data:

LOAD If(Service='', null(), Service) as Service, People;

LOAD * Inline [

Service,People

A,200

B,34

,676

,90

];

If I use the dimension if( aggr(rank(sum(People)),Service)<=2,Service)

If I use

=if( aggr(rank(sum({<Service={"*"}>} People)),Service)<=2,Service)

The difference between those two is that in the first, te null() service is on top 2, and Service B goes to Top3, so it doesn't show

In both I used sunindia's expression: =Sum({<Service = {'*?'}>}People)