How to query the count where minus between 2 numeric fields and less than 0

Hi there

I have 2 fields called Value1 and Value2 on the datatype and they are both numeric fields.

I want to query as follow:

Select ALL records where (Value1 - Value2) < 0
Then count how many records filtered above

How do I do this?


Hi there, @partners… you can do it with an advanced filter, but those kinds of filters are not very efficient. So, you might want to consider adding a new field to the data type that stores the difference between value 1 and value 2, and then you can do the count directly on the new field.

Hope this helps.


Thanks @mikeloc.

I was thinking to do that as per your suggestion to have field for difference between value1 and value2.

Where I can find this advanced filter again? Is this under :filter?

At the end of your search query, click (More…) and select the :filtered option. Then, add a constraint, and the Advanced… option is at the bottom of the dropdown.

Hi @mikeloc … I didn’t see the Advanced option under :filtered and contraint. Am I missing something here?

1 Like

Got it. Thank you. Will explore this option further.

I’m coming from SQL database … and this type of query is a bit confusing sometimes especially for complex ones. :slight_smile: Is there any way to see this troubleshoot this query?

Hi @mikeloc

Managed to get this to work - thanks again :+1: :+1:

1 Like