Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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