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

partners · 2020-12-25T17:46:30.838Z

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?

Thanks

mikeloc · 2020-12-25T18:27:07.522Z

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.

Best…
Mike

partners · 2020-12-25T18:32:51.522Z

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?

mikeloc · 2020-12-25T18:36:17.919Z

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.

partners · 2020-12-25T18:59:16.186Z

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

mikeloc · 2020-12-25T19:06:17.060Z

advanced700×445 19.1 KB

partners · 2020-12-25T19:21:48.572Z

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. Is there any way to see this troubleshoot this query?

partners · 2020-12-25T20:01:16.611Z

Hi @mikeloc

Managed to get this to work - thanks again

Capture_01958×768 63.1 KB