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

partners · December 25, 2020, 5:46pm

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 · December 25, 2020, 6:27pm

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 · December 25, 2020, 6:32pm

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 · December 25, 2020, 6:36pm

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 · December 25, 2020, 6:59pm

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

mikeloc · December 25, 2020, 7:06pm

partners · December 25, 2020, 7:21pm

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 · December 25, 2020, 8:01pm

Hi @mikeloc

Managed to get this to work - thanks again