skipping the entire row if there is null

[#1]

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

[crusader_ · Accepted solution]

Hm..

I've changed your scrip like this:

LOAD * INLINE [

   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()*avg()>0,

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

     /

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

     )

Hope this helps.

Andrei