Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
crusader_
Partner - Specialist
Partner - Specialist

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

View solution in original post

10 Replies
Not applicable
Author

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)

crusader_
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

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 applicable
Author

Not working in this scenario

Thanks for replying

crusader_
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

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

crusader_
Partner - Specialist
Partner - Specialist

Use

if( *>0,

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

     /

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

for your example.

It will work.

PFA

Andrei

Not applicable
Author

Crusader_

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 

Not applicable
Author

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