Conditional row filter for slice?

[Paul_Janson]

I have a slice called ThisWeek that pulls from the data table all data that were entered during the current week. Since our week is Monday - Sunday, I had to do a little cajoling to get the appropriate data to show when a user is viewing the dashboard on a Sunday-

OR(

AND(WEEKDAY(TODAY())>1,
WEEKNUM(TODAY())=WEEKNUM([Date]),
YEAR(TODAY())=YEAR([Date])),

AND(WEEKDAY(TODAY())=1,

OR([Date]=TODAY(),

AND(WEEKNUM(TODAY())-1=WEEKNUM([Date]),
WEEKDAY([Date])>1)),

YEAR(TODAY())=YEAR([Date])
)
)

The first AND condition applies to viewing the dashboard on Mon-Sat and is pretty straight forward. The second AND condition is for viewing on Sunday. Basically, it says that if today is Sunday, allow data from today and from last week, as long as it’s not last week Sunday.

Two questions- is there any way to create a test environment to test different hypothetical dates? I made some adjustments to the formula just now and won’t be able to see if it works until it’s Sunday! In the spreadsheet version of this prototype, we had a cell that, when empty, all formulas would operate under today’s date, but when a test date was entered, all formulas would operate under the test date. Any way to replicate this in AppSheet?

Second question- I’m realizing the need to see a view of LAST week’s data, and ideally any past week’s data. I started creating a LastWeek slice, but then realized that my dashboard view has 6 other views, each with their own slices. I would need to copy all views and all slices and connect them to a LastWeek slice. Hoping for a better solution. Is there any way to create a slice such that any week (or at least last week) can be selected and viewed?

[Steve]

[Date] is in this Monday to Sunday?

AND(
  ([Date] >= (TODAY() - WEEKDAY(TODAY()) + 2)),
  ([Date] <= (TODAY() - WEEKDAY(TODAY()) + 8))
)

[Paul_Janson]

That’s very slick. I just tested it in ThisWeek slice and it works like a charm. Thank you!!

This eloquently restates my cumbersome formula and eliminates the need for a test date, but it still doesn’t help me with the main issue, which is how to selectively show last week’s data (or any week’s) without having to create a completely new slice called LastWeek, as well as a half dozen new slices and views that populate my dashboard.

It would be awesome to select “0” for this week, “1” for last week, “2” for 2 weeks ago, etc.

[Steve · Accepted solution]

[Date] is in the Monday to Sunday week of so-many Weeks past ago?

AND(
  ([Date] >= (TODAY() - WEEKDAY(TODAY()) + 2 - (USERSETTINGS("Weeks past") * 7))),
  ([Date] <= (TODAY() - WEEKDAY(TODAY()) + 8 - (USERSETTINGS("Weeks past") * 7)))
)

Set Weeks past to 0 for this week.

[Paul_Janson]

Pure gold! Would never have thought to use USERSETTINGS this way. Thank you!