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.

[#2]

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.

[#3]

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.

[rubenmarin]

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?

[#5]

Hi,

it shows Error in Calculated Dimension.

any reason why?

Thanks.

[rubenmarin]

If is this you can change calculated dimension to:

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

[rubenmarin]

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

[#8]

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?

[#9]

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.

[#10]

Hi Thabiso

try this as the calculated dimension

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

/Teis

[rubenmarin]

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)