How to Exclude Null Values from Calculated Expression?

[#1]

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.

[rubenmarin]

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

[maleksafa]

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

[sunny_talwar]

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

HTH

Best,

S

[#5]

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.

[sunny_talwar]

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;

[luciancotea]

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

[sunny_talwar]

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,

S

[#9]

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.

[rubenmarin]

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