sharonhaviv's avatar
sharonhaviv
Data Storage
07-16-2025
Status:
New Idea

OR filter inside a formula

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.
 
Dashboards & Analytics

3 Comments

  • david-h's avatar
    david-h
    Cloud Apps
    07-17-2025

    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.

  • Norajohnson's avatar
    Norajohnson
    Data Storage
    07-19-2025

    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.

  • wallingfordce's avatar
    wallingfordce
    Data Pipeline
    07-29-2025

    ^ 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
    )
  )
)