Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
steveb24
Contributor II
Contributor II

Sum while excluding null IDs filtering

I

Lead ID Returned ID Price
1 1 5
2 2 10
3   15
4 null 20

 

Trying to sum the price only for those leads that have a return ID.

I've tried 

=sum({<returned_id={=len(trim(returned_id))>0} >} price)

as has been suggested in similar topics, but I'm getting 50 as my sum rather than 15.

I've also tried other suggestions like

=sum({<returned_event_id={*}>} price)

but that gives me a very small sum and not the full amount I should be getting.

Labels (3)
3 Replies
JonnyPoole
Former Employee
Former Employee

 

You can try a search mask for any value >= 0  

 

Sum( {<[Returned ID]={">=0"}>} Price)

 

JonnyPoole_0-1709840368028.png

 

steveb24
Contributor II
Contributor II
Author

This is giving me the same result as =sum({<returned_event_id={*}>} price)

not capturing everything.

JonnyPoole
Former Employee
Former Employee

Try loading the returned_event_id as a num() field in the data load.

LOAD

    num(returned_event_id) as returned_event_id

that should make sure you are dealing only with number values.  

Also, maybe don't include zero values: 

Sum( {<[Returned ID]={">0"}>} Price)