Widget Filters - condition across multiple fields
Working as a dashboard designer without access to the back end data. I am trying to filter with an 'or' condition across a number of fields.
I can only see how to apply a single field filter on the front end, which would equate to an and condition across the field.
e.g. data to identify high priority records are where Field_1, Field_2, Field_3 or Field_4 are equal to "Yes".聽
AlexW
Posted 1 year ago路Last reply 1 year ago
12 comments
ramansingh89
路1 year agoJake_Raz聽Do you think your suggestion can help resolve my use-case as well ? Here is the description
https://community.sisense.com/t5/help-and-how-to/single-filter-against-multiple-columns/td-p/27079
聽
Jake Raz
路1 year agoPerhaps. I'll take a look and post a reply there 馃檪
DRay
路1 year agoHi聽AlexW,
Would you like me to get the ball rolling on connecting you with a technical resource who will work with you on this?
AlexW
OP1 year agoJake_Raz聽I have attempted this with an or but got no results, should I be structuring this differently?
Jake Raz
路1 year agoAlexW聽Hmm. Without seeing your underlying data, I couldn't say, sorry. I will say that, whenever I need to create formulas like this, I often run into issues with getting it to work in the way I'm expecting. I usually just keep experimenting (and searching the Sisense forums for useful info) until I find something that works.
Perhaps there's an issue with one or more of the conditions? One thing you could try is to filter down to a particular record in your data that you聽know should be in the final output. Like, if you know Matter123 should show in the view because it meets all the conditions, then add a widget filter for the matter name and specify that one matter by itself. After that, try creating each condition as its own separate formula. Like, instead of chaining all them together with ORs/ANDs in the same formula, you would have each condition in its own formula that resolves to a 1 or 0. Then you can add them all to the view and validate, separately for each condition, whether it's correctly resolving to a 1. In order for your chained OR formula to work, at least one of the conditions must be resolving to a 1 (or if you're using AND, then all of them must).
Another common issue to run into with this sort of formula are error messages in the formula editor. You didn't mention this but it might be something you encounter. Often this is because the underlying data query is pulling in multiple rows of data per item in the view, so you have to aggregate it somehow by wrapping certain elements of the formula in MIN, MAX, SUM, etc.
Good luck! It can definitely be fiddly, sometimes. If you can't find a way to achieve this through formulas then you may need to look into the other solutions suggested.
DRay
路1 year agoHello聽AlexW,
I鈥檓 following up to see if any of the solutions offered worked for you.
If so, please click the 'Accept as Solution' button on the appropriate post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.
Thank you.
Jake Raz
路1 year agoMaybe try using Measured Values? You could create a formula, using an IF statement and chaining together your conditions with AND. If all conditions are true, output 1, else 0. Then you filter on that formula for only the items/rows with a "1".
The tricky part is that, within the formula, when you're setting the conditions, you can't just say something like:
This is because the IF formula only really works when you're comparing numerical values. However, you can construct the formula such that it still results in the same outcome.聽To do so, when you're in the formula editor, add two parentheses - "(" and ")" - and then inside聽the parentheses do the following:聽
It should look something like this:
Then you would click on the second item and select "Filter", and select the Yes value (or whatever value you're looking for. This will ensure that you're filtering down to only the records/items in the widget that contain a Yes value in that field.聽
You can use this in an IF formula. If the condition evaluates to true, output a 1, else output a 0 (or NULL). Then you can just chain together multiple statements with AND's, so it only outputs the 1 if聽all聽conditions are true.
The final formula would look something like this:
(Remember, you also need to click on each of those Fields and select "Filter" so it knows what it's looking for.)
Then you filter your widget for when that formula is equal to 1. The nice thing is that, after you have the filter in place, you can hide the original formula so it doesn't display in the actual widget (since it was only necessary for the filtering).
mcaleargroupIT
路1 year agoThank you, came searching through the forum looking for a solution to this exact problem, but needed to add multiple conditions dependent on the filters set for the field. Was able to use your method with a little tweaking and wanted to share for anyone else stuck with the same issue.
AlexW
OP1 year agoIs it possible to do this filtering on a widget rather than a whole dashboard?
harikm007
路1 year agoHi聽AlexW聽,
Please check if the solution below works for you
https://community.sisense.com/t5/knowledge-base/how-to-use-quot-or-quot-filter-condition-on-a-widget-level/ta-p/20948
-Hari
DRay
路1 year agoHello聽AlexW,
I鈥檓 following up to see if the solution offered by聽AssafHanina聽worked for you.
If so, please click the 'Accept as Solution' button on the appropriate post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.
Thank you.
Assaf Hanina
路1 year agohey聽AlexW聽,
As a Dashboard Designer, you have the ability to add filters and change the Operand between the Filters.
the Default is AND and it can be changed to OR as well.聽
there are prerequisites which require to Validate with Sisense Admin.
Please refer to the official documentation of the Filter Relationships Editor
Best Regards
聽