Conditional Filter

#1

Hello,

I would like to know if it is possible to apply a filter just in certain cases. For example, I tried to use the following code, but I get an error:

CASE WHEN ‘Condition’ THEN FILTER… END

Could you tell me how should I write a conditional filter, please?
Thank you in advance.

Best regards,
Catarina

#3

Hi,

Make condition part of the filter. I.e. if you want to apply size filter only to the red apples write
FILTER “apples”.“color” != ‘red’ OR “apples”.“size” > 50;

#4

Hi,

Thank you for your answer, but I think this does not apply in my case.
What I intend to do is to apply a filter only if something happens. For example, we have a variable that can be empty and I just want to apply the filter when this variable is not empty.

I have tried several things, such as:
CASE WHEN VARIABLE != ‘’ THEN FILTER … END

Could you tell me how should I write this filter, please?
Thank you in advance.

Best regards,
Catarina

#5

Hi,

As i understood, You can try with this FILTER ISNULL(Variable)=0, it will hide all the empty value then you can apply your filter with the Variable that you want to look for i.e for your Example CASE WHEN VARIABLE ‘’ THEN …ELSE NULL END

With Regards

Rahul

#6

Hi Catarina and Rathul,

Just wanted to add that evaluating a variable with the ISNULL() function has never worked for me (as you’ve probably already discovered). I believe the reason is ISNULL() expects a column, and therefore cannot be used to evaluate a single value such as a variable. It would be great if there was some other function that we could use to determine whether a variable is null, but unfortunately I haven’t come across a way to handle null variables yet.

-Tyler

#7

Hi Tyler, hi Rahul

Thank you both for your answers.

A variable can’t be null and that’s why the function ISNULL() doesn’t work with variables. A variable can just be empty. So, if we are working with variables we have to write:
’VARIABLE’ = ''

Maybe I’m not explaining myself well, but that’s not what I mean, Rahul. What I intend to do is apply a filter only if something happens. For example, I want to apply a filter to the CASES.CASE_ID column only if my variable is not empty.
Something like:
CASE WHEN ‘VARIABLE’ != ‘’ THEN CASES.CASE_ID = ‘…’ END
I do not know if Celonis has this functionality implemented. If I can solve this problem, I’ll put the solution here.

Best regards,
Catarina Amaral

#8

Hi Catarina,

You can not use condition in filter. CASE WHEN ‘VARIABLE’ != ‘’ THEN CASES.CASE_ID = ‘…’ END would not work

However you can achieve exactly the same result if you use following filter
FILTER <%= VARIABLE %> = ‘’ OR CASES.CASE_ID = ‘…’ ;

If your variable is empty first condition in filter will be TRUE and even if second condition is FALSE will see all cases. If your variable is not null, you will need second condition to be true for line to appear, so will see only cases matching the condition.

#9

Hi Nicks,

I’ve tried this approach, but I have this error. Do you know how I can solve it?
image

Thank you in advance.

Catarina

#12

Hi Caterina,

Is your variable something that the viewers can/should be able to set in your analysis?

I.e. is the filter something you want them to be able to switch on and off?

If that’s the case, then here is a possible solution:

  1. Make a Button Dropdown, select "Manual Input" and then add two dropdown entries, which both have the Button Action "Set Variable" (select your Variable).
  • Call one something like "Filter ID" and set the value to "FILTER CASES.CASE_ID = ‘[insert relevant Case ID]’".

  • Call the other somethings like “Don’t Filter ID” and set the value to " ‘’ " (two single quotation marks).

Then if you select "Filter IDs" then the Variable will have the Filter condition as its value.

Filter%20IDs%20Button%20Dropdown

  1. Go to the analysis/sheet/component filter and just write "<%= VARIABLE %>"

When you have done that then the filter will only be active when “Filter ID” has selected from the dropdown thereby setting the variable to the filter condition. If you choose “Don’t Filter ID” the filter condition will just be ‘’, which doesn’t exclude any cases whatsoever.

I hope this helps.

Best regards,

Calandra (Celonis Data Science Team)

1 Like
#13

Hi,

I’m facing a similar issue in that there seems to be no way to determine that a Variable has no value currently assigned to it.

I have a button drop down with ‘Load Entries’ that loads all distinct regions in my model.

When the button drop down has nothing selected I’d like to do two things:

  1. Modify my sheet filter so that no filter is applied (currently this throws an error because it is expecting a value in the variable my button drop down as written to)
  2. Modify my text component to show that “All Regions” are being displayed (<%=Variable1%>=’’ doesn’t seem to evaluate to TRUE when there is nothing written to the variable)

Any ideas?

#14

Hi,

in answer to your first question, I would turn the solution on its head. Instead of selecting no regions I would use the dropdown to select all Regions and then use a IN command in the filter statement, which would look like this:

FILTER "table"."regions" IN (<%= regions variable %>);

That way only cases from those regions will be used that are selected, however many are selected. If all regions are selected then there is no restriction and all cases are included.

Its important that the dropdown button has the following settings:

  1. Allow multiple selections is activated

  2. The Separator is a "," (comma)

  3. The wrapping Characters are “’” (single Quotation marks)

When you publish the analysis make sure all regions are selected in the dropdown so that this become the default that viewers see when they open the analysis.

As for your second question about writing “All Regions” when there are no restrictions on the regions displayed, I’m still working on that and will try to post a solution soon.

Best regards,

Calandra (Celonis Data Science Team)

#15

Thank you for your prompt response.

Your response got me thinking - Actually my analysis is set up to display data on a per-region basis only. Displaying everything/or data related to groups of regions is not of interest.

So actually, I specifically only want the drop down to have only one selection at a time. Which means that I don’t want anything displayed if no selection has been made. I think the issue here is that the drop-down has no setting which ensures that at least one value is selected. And there is no way to alert the user that they should be making a selection because you can’t reason with an empty variable.

So it would be good to understand how to reason with a variable which currently has nothing assigned to it…