Forum Discussion

Jake_Raz's avatar
Jake_Raz
ETL
09-18-2024

Filter by multiple conditions using OR logic

Hello! I'm trying to figure out how to write a formula using measured values that will let me filter down to records meeting either Condition A or Condition B. I can't use the normal filter UI since it operates purely on AND logic. If I add both Condition A and Condition B to the filter panel then it'll only show records that satisfy both requirements. I want records that satisfy either requirement.

Normally I would achieve this through formulas (i.e. measured values). I'd use an IF statement and list out the conditions, and combining with either AND's or OR's depending on what I'm trying to do. Ultimately, everything should resolve to either a 1 or 0, then I'd filter on that (e.g. only show me records that resolve to a "1", meaning they satisfied the requirements in my formula).

However, for this particular example, I've been unable to get this to work correctly, so I thought I'd try using the "advanced filter" in the normal Filter UI. I found this older archived/legacy post that looked promising: https://community.sisense.com/t5/build-analytics/dashboard-filter-date-in-last-180-days-or-null/m-p/4031

The example in that post is roughly the same thing I'm trying to do: filter a certain date to show EITHER the last 90 days OR nulls. In other words: show me only the blanks, or if it's NOT blank, then only if it's in the last 90 days. Make sense?

Based on that post I linked above, here's the formula I came up with:

{
  "or": [
    {
      "last": {
        "count": 90,
        "offset": 0
      },
      "custom": true
    },
    {
      "include": {
        "members": [
          null
        ]
      }
    }
  ]
}

This works, in the sense that it doesn't give me any error messages about syntax or invalid query. However, it's exhibiting odd behavior. While it's including the nulls, which I want, the "last" filter is doing the opposite of what I want. Instead of showing the last 90 days, it's showing everything older than 90 days:

If I remove the "include...members" part, and use only the "last" part by itself, THEN it works correctly (note the dates are now correctly in the last 90 days):

How can I achieve both at once? Show the nulls plus only dates in the last 90 days?

If you can suggest a measured value formula I could use instead, then I'll use that method instead. 

 

Dashboards & Reporting

2 Replies