Skip to main content
Solved

How to apply Conditions to Filters?

  • July 28, 2021
  • 3 replies
  • 19 views
L
Level 3
Forum|alt.badge.img+11

Example:

CASE WHEN YEAR("X"."y") >= 2019 THEN FILTER ROUND_DAY("X"."y") != ROUND_DAY("A"."b") OR ROUND_DAY("X"."y") != ROUND_DAY("M"."n")

OR ROUND_DAY("A"."b") != ROUND_DAY("M"."n") ELSE FILTER ROUND_DAY("A"."b") != 2019-01-01 OR ROUND_DAY("M"."n") != 2019-01-01 END

 

Neither CASE WHEN nor IF worked for me. I'm trying in a Component Filter, though a solution on a Sheet level could also solve my problem.

Best answer by joos.buijs

Hi @luis.magal ,

 

I would rewrite your filter to this form:

FILTER (... AND ... ) OR ( .... OR ...)

 

roughly:

FILTER (YEAR("X"."y") >= 2019 AND (ROUND_DAY("X"."y") != ROUND_DAY("A"."b") OR ROUND_DAY("X"."y") != ROUND_DAY("M"."n")

OR ROUND_DAY("A"."b") != ROUND_DAY("M"."n") ) OR (ROUND_DAY("A"."b") != 2019-01-01 OR ROUND_DAY("M"."n") != 2019-01-01)

 

I found that filters inside case when statements won't work, but a FILTER using AND/OR conditions will work.

 

Please let me know if this works!

3 replies

joos.buijs
Level 10
Forum|alt.badge.img+2
  • joos.buijs
  • Level 10
  • Answer
  • August 2, 2021

Hi @luis.magal ,

 

I would rewrite your filter to this form:

FILTER (... AND ... ) OR ( .... OR ...)

 

roughly:

FILTER (YEAR("X"."y") >= 2019 AND (ROUND_DAY("X"."y") != ROUND_DAY("A"."b") OR ROUND_DAY("X"."y") != ROUND_DAY("M"."n")

OR ROUND_DAY("A"."b") != ROUND_DAY("M"."n") ) OR (ROUND_DAY("A"."b") != 2019-01-01 OR ROUND_DAY("M"."n") != 2019-01-01)

 

I found that filters inside case when statements won't work, but a FILTER using AND/OR conditions will work.

 

Please let me know if this works!

L
Level 3
Forum|alt.badge.img+11
  • luis.magal
  • Author
  • Level 3
  • August 11, 2021

Hi @luis.magal ,

 

I would rewrite your filter to this form:

FILTER (... AND ... ) OR ( .... OR ...)

 

roughly:

FILTER (YEAR("X"."y") >= 2019 AND (ROUND_DAY("X"."y") != ROUND_DAY("A"."b") OR ROUND_DAY("X"."y") != ROUND_DAY("M"."n")

OR ROUND_DAY("A"."b") != ROUND_DAY("M"."n") ) OR (ROUND_DAY("A"."b") != 2019-01-01 OR ROUND_DAY("M"."n") != 2019-01-01)

 

I found that filters inside case when statements won't work, but a FILTER using AND/OR conditions will work.

 

Please let me know if this works!

Thanks, Joos! It worked in 1 of the 2 situations, but the path is clearer.

joos.buijs
Level 10
Forum|alt.badge.img+2
  • joos.buijs
  • Level 10
  • August 11, 2021

Thanks, Joos! It worked in 1 of the 2 situations, but the path is clearer.

Let me know if you need help in situation #2 :)

Community Code of ConductAccessibility statement