OR filter inside a formula

Summary: m5hn8p is looking to modify a formula in their dashboard to use OR logic across multiple fields instead of the default AND logic provided by commas in the RSUM() function. m5xpp8 suggests using a complex JAQL custom filter or adding up the RSUM of individual conditions as an alternative. m9a5uz recommends using the FILTER function with OR logic to handle up to 255 conditions, while chris proposes a similar RSUM setup but cautions about potential double-counting issues.

I'm trying to modify a formula in my dashboard that currently uses multiple fields as filters inside an RSUM() aggregation. My goal is to apply OR logic across these fields rather than the default AND logic imposed by comma separation.

Here is the original formula:

RSUM([# of unique INCIDENT_ID],

[activity type],

[activity type1],

[activity type2],

[activity comment],

[activity comment1],

[_NUMBER],

[_NUMBER1],

[activity type3],

[time to resolve (mins)],

[ASSIGNED_TO])

As I understand, the commas between the fields act as AND conditions. I attempted to change the commas to use OR, but received a syntax error.

I want to apply OR conditions across these fields, so that if any one of them meets a specific value or condition, the row will be included in the aggregation.

sharonhaviv

Posted 1 year ago·Last reply 1 year ago

3 comments

Chris Wallingford

·1 year ago

^ This, or even...

RSUM (
([# of unique INCIDENT_ID],[activity type]) +
([# of unique INCIDENT_ID],[activity type1]) + 
([# of unique INCIDENT_ID],[activity type2]) +
etc...
)

Although, this will double count INCIDENT_ID that meet multiple criteria. Maybe what you needs is...

RSUM (
  SUM ( [INCIDENT_ID] ,
    IF (
      ([# of unique INCIDENT_ID],[activity type]) +
      ([# of unique INCIDENT_ID],[activity type1]) + 
      ([# of unique INCIDENT_ID],[activity type2]) +
      etc... > 0
      , 1 , 0
    )
  )
)

Norajohnson

·1 year ago

The FILTER function allows you to filter a range of data based on criteria you define. In the following example we used the formula Filter. Select monkey type Text Filters or Number Filters, and then select a comparison, like Between. Enter the filter criteria and select OK.. It is used for logical tests and can handle up to 255 conditions. The function's syntax is =OR(logical1, [logical2], …). It is often combined with other functions like IF and AND for complex decision-making processes.

david-h

·1 year ago

Hi sharonhaviv ,

commas do act as an AND and I am to sure there is a syntax to change it as an OR. It is possible that some undocumented function exists though, but there are some solutions that you can use.

There is one way of doing this through a complex JAQL custom filter replacing all of the "commas condition". The filter will be on INCIDENT_ID (same one os in the RSUM) and will use OR conditions.

Another way is to add the RSUM. I think the logic will be the same : RSUM([# of unique INCIDENT_ID],cond1,cond2, cond3, ...) with commas = OR is equivalent to "RSUM([# of unique INCIDENT_ID],cond1) + RSUM([# of unique INCIDENT_ID],cond2) + RSUM([# of unique INCIDENT_ID],Cond3), ...)

and in order to simplify the syntax, you can add to your data model some calculated fields that concatenate some of your conditions. For instance the 4 activity type. You separate the four fields by a string separator (, or ; or | or whatever) and then you condition can be different "contains" combine by OR.

hope this helps.

Best, David.