10 Replies Latest reply: Sep 25, 2014 7:06 AM by Andrei Kaliahin

# skipping the entire row if there is null

 Client Service ID Client Service Name NSR No of hours DTGL503010000 Corporate Finance Advisory 0 591.5 DTGL503040000 M and A Transaction Services 3335613 2139.1 DTGL503050000 Valuation Services 114000 74

if ([SERVICE_LINE_CY_YTD_TOTAL_HOURS_ALL_ENTITY_AMOUNT]<0 and [SERVICE_LINE_CY_YTD_NSR_ALL_ENTITY_AMOUNT]> 0) then (null)

else if ([SERVICE_LINE_CY_YTD_TOTAL_HOURS_ALL_ENTITY_AMOUNT]>0 and [SERVICE_LINE_CY_YTD_NSR_ALL_ENTITY_AMOUNT]< 0) then (null)

else ([SERVICE_LINE_CY_YTD_NSR_ALL_ENTITY_AMOUNT]/[SERVICE_LINE_CY_YTD_TOTAL_HOURS_ALL_ENTITY_AMOUNT])

I have a situation that should calculate the sum(NSR)/sum(no of hours) but It should calculate the sum(NSR) and sum(no of hours)

when the number is neither positive nor negative

If it is either positive or negative or zero then it should skip the entire row and calculate

Ps: if needed any information i can give bit more information but not an sample application

• ###### Re: skipping the entire row if there is null

In the above example NSR has a value 0 .so it should skip that row and therefore 591.5 shouldn't be added to the sum(no of hours)

• ###### Re: skipping the entire row if there is null

Hi,

Try this one:

= if( [SERVICE_LINE_CY_YTD_TOTAL_HOURS_ALL_ENTITY_AMOUNT]*[SERVICE_LINE_CY_YTD_NSR_ALL_ENTITY_AMOUNT]>0,

sum({\$<NSR-={"0"}>} NSR)

/

sum({\$<NSR-={"0"}>} [no of hours])

)

Hope this helps you.

Andrei

• ###### Re: skipping the entire row if there is null

Adding to Andrei, you can also try the other way(sometimes "0" does not work:

sum({\$<NSR={">0"}>} NSR)

/

sum({\$<NSR={">0"}>} [no of hours])

Thanks,

Singh

• ###### Re: skipping the entire row if there is null

Yeah, I agree somtimes "0" doesn't work.

But if we use NSR={">0"} we exclude cases in SUM() function where NSR and TOTAL both negative.

What exactly "Not working" means? (null, reduced data or incorrect calculation or smth else)

Thanks

Andrei

• ###### Re: Re: skipping the entire row if there is null

Andrei Kaliahin

Not working here represents in the given expression given by you only 1st column value will be verified that the value is negative or positive and it should imply to the next column also so it should check whether the value is positive or negative

• ###### Re: Re: skipping the entire row if there is null

But when I have two negative values it doesn't work for two positive values it works fine .I am attaching a sample file may be this could help you

But in the sample app my dimension will be not be unique so if I have two years in common then it should add the two years data and followed by that condition

• ###### Re: skipping the entire row if there is null

Use

if( [A]*[B]>0,

sum({\$<A={">0","<0"}>} A)

/

sum({\$<A={">0","<0"}>} [B]))

It will work.

PFA

Andrei

• ###### Re: Re: skipping the entire row if there is null

Thanks Andrei thats working but when i have two same years it is not working fine

suppose consider 2009 as 2008 so there will be 2 2008 , then its not working

So, 2008 values should be added and then the expression is to be done

• ###### Re: skipping the entire row if there is null

Hm..

I've changed your scrip like this:

year, A, B, C

2008, 0, 6, 89

2009, -6, -7, 6

2009, -5, -9, 44

2010, 18, 8, 89

2011, -, 7, 4

];

And if I reload file 2009 year excluded, because it set in dimension and when you're trying to check if(A*B>0) condition for 1 dimension it find 2 values without aggregation it gives null.

Solution is or to make unique field in dimension, or use aggregation function sum() , avg() etc

in below way:

if( avg([A])*avg([B])>0,

sum({\$<A={">0","<0"}>} A)

/

sum({\$<A={">0","<0"}>} [B])

)

Hope this helps.

Andrei

• ###### Re: skipping the entire row if there is null

Not working in this scenario