Expression with And & Or

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

Maybe this?

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

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?

2 Likes

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.

2 Likes