Expression with And & Or

[Alex3]

Hello,

I am trying to create a slice that will select cars that have:

1. A certain rental status (either 2, 5, or 3) and
2. A rental date that is between 14 days old and 7 days after the current date.

Below is the slice expression that I wrote. The expression is not quite capturing the correct records.

AND
(OR([RentalStatusID]=2,[RentalStatusID]=5,[RentalStatusID]=3),
OR((AND([RentalDate] >= (TODAY() - 14),[RentalDate] <= TODAY())),
AND([RentalDate] <= (TODAY() + 7),[RentalDate] <= TODAY())))

Any ideas?

Thanks,

Alex

[Marc_Dillon]

Maybe this?

AND(
  IN( [RenstalStatusID] , LIST( 2,3,5 ) ) ,
  [RentalDate] >= TODAY()-14 ,
  [RentalDate] <= TODAY()+7
)

[Steve]

Here’s your expression reformatted for clarity:

AND(
  OR(
    ([RentalStatusID] = 2),
    ([RentalStatusID] = 5),
    ([RentalStatusID] = 3)
  ),
  OR(
    AND(
      ([RentalDate] >= (TODAY() - 14)),
      ([RentalDate] <= TODAY())
    ),
    AND(
      ([RentalDate] <= (TODAY() + 7)),
      ([RentalDate] <= TODAY())
    )
  )
)

The logic expressed above is:

1. All of these must be true:
2. ...The rental status ID is 2, 3, or 5.
3. ...At least one of these must be true:
4. ......Both of these must be true:
5. .........The rental date is on or after two weeks ago.
6. .........The rental date is on or before today.
7. ......Both of these must be true:
8. .........The rental date is on or before a week from today.
9. .........The rental date is on or before today.

Looks to me like (8) and (9) aren’t quite right: if (8) is true, (9) is always true, as today is always on or before a week in the future. Perhaps this is the problem?

[Alex3 · Accepted solution]

Marc_Dillon:

AND(
  IN( [RenstalStatusID] , LIST( 2,3,5 ) ) ,
  [RentalDate] >= TODAY()-14 ,
  [RentalDate] <= TODAY()+7
)

Thank you Marc and Steve,

After I changed the
’ [RentalDate] <= TODAY()’
to
[RentalDate] >= TODAY()

, at the end of expression, the formula worked.

Marc’s expression worked as well. I learned something new with the LIST expression.