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
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)
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
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
Not working in this scenario
Thanks for replying
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.
to bhanukiran
What exactly "Not working" means? (null, reduced data or incorrect calculation or smth else)
Please share screenshots.
Thanks
Andrei
Hi Angad,
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
Use
if( *>0,
sum({$<A={">0","<0"}>} A)
/
sum({$<A={">0","<0"}>} ))
for your example.
It will work.
PFA
Andrei
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
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