Identifying Deals where "Property A" is greater than "Property B" ?

[MattDavirro]

Hi there.

I'm wondering if there is away to filter (or trigger a workflow) based on a comparison of two properties?

ex. I want to report on all Deals where "Property A" is greater than "Property B" Is there a way I can do this?

OR Can I have a workflow set "Property C" to True if "Property A" is greather than "Property B" ? Then I can filter by that property

[karstenkoehler · Accepted solution]

Hi @MattDavirro,

Yes and no. By default, this is not possible. Number filters in lists and workflows only accept numbers, not other properties:

Buuut it looks like you're on an Enterprise subscription which gives you access to custom calculation properties. These types of properties allow you to set up custom equations based on number properties.

You would create such a property and subtract property B from property A. If this property value is negative, you know that B must be greater than A. If it is positive. A must be greater than B. You can now use your new custom calculation property in lists and workflows.

Let me know if you have any follow-up questions!

[chris_p_lukes]

I've used this calculated property workaround before, and it's a good workaround.  That said, for some applications it can be a bit clunky to have to create an additional property for each possible comparison one might want to make.

For a reporting use-case, there would perhaps be a smaller number of meaningful "reporting metrics" (or reporting filters) derived from comparing a pair of number properities, and where having a calculated property might be helpful for pulling into various reports.

My use-case is often for list filters and/or workflow enrollment and if/then branching criteria (follow Branch A vs. B, based on whether Property X or Y is greater), where a given comparison might be only relavent to a single particular workflow or list (and may only be needed for a temporary application) – it would be nice to be able to dynamically define a comparison on the fly, which only "lives" within the list or workflow that uses it to keep properties less cluttered, and lets the user stay in the List or Workflows tool instead of jumping over to Property Settings midway when they realize the need for the comparison.  In the event many lists or workflows may need the same comparison, then of course a calculated property may be helpful.

Adding to the limitation here, the fact that calculated properties would be blank if one of the two compared properties are unknown creates the need for handling this exception – e.g.:

"Property A minus Property B" Calculation Property > 0
OR
Property A > 0 AND Property B is unknown
(or similar)

Ideally we could have better handling of "blanks" whether this is within Calculation properties (e.g., option to "treat blanks as a zero" or "don't calculate if any of the properties are blank [as is the current functionality]") or within the requested filter logic allowing comparing two properties (e.g., option to treat unknown properties as zeros, or perhaps always consider a known property greater than an unknown property, even if the known property is negative, depending on the use-case).

[warrendavey]

Yes, you can use a calculation field to subtract one property from the other.  If that property is negative then you know it is not true.  You can then filter a report without the negatives.