Creating a slice based off of Date range and Day of the week

AppSheet AppSheet Q&A
1

Good Afternoon,

I’ve made a slice that is capable of showing information only for the user’s chosen date range. I would like to take this one step further and allow them to view the data for only the chosen day of the week within that date range. So for example, only viewing Thursdays from 09/01/20-09/30/20.

This is what I have so far. How would I change this so that it only shows the chosen day of the week?

IN( [Date:], SELECT(Class History[Date:], AND( [Date:]>=ANY(Date Filter[Start Date]), [Date:] <=ANY(Date Filter[End Date]))))

Thank you,

Kat

2

Like this?

IN(
  [Date:],
  SELECT(
    Class History[Date:],
    AND(
      ([Date:] >= ANY(Date Filter[Start Date])),
      ([Date:] <= ANY(Date Filter[End Date])),
      IN(TEXT([Date:], "DDDD"), Date Filter[Day of the week])
    )
  )
)
1 Like
3

Thank you Steve for your help. Unfortunatley when I input the following I get no results.

IN(
[Date:],
SELECT(
Class History[Date:],
AND(
([Date:] >= ANY(Date Filter[Start Date])),
([Date:] <= ANY(Date Filter[End Date])),
IN(TEXT([Date:], “Sunday”), Date Filter[Day:])
)
)
)

At the current moment the Class History table is the one with the majority of the information, including the days of the weak ([Day:] column). The Date Filter table only has a column for the date start and date end. Do I need to adjust this?

Thank you so much for your help!

Kat

4

Kat:

TEXT([Date:], “Sunday”)

What is this for? I suggested:

Steve:

TEXT([Date:], "DDDD")

1 Like
5

I assumed “DDDD” was what you meant for the week day. Was it supposed to be for something else?

Thank you!

6

I figured it out! I plugged the following expression into slices for each individual day of the week:

AND(CONTAINS([Day:], “Sunday”),
IN( [Date:], SELECT(Class History[Date:], AND( [Date:]>=ANY(Date Filter[Start Date]), [Date:] <=ANY(Date Filter[End Date])))))

Thank you so much for your help!

Kat

1 Like
7

Kat:

I assumed “DDDD” was what you meant for the week day.

Nope. It’s supposed to be the literal text, DDDD (four uppercase Ds).

2 Likes
8

That’s !!