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?
Solved! Go to 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.
[Date]
is in this Monday to Sunday?
AND(
([Date] >= (TODAY() - WEEKDAY(TODAY()) + 2)),
([Date] <= (TODAY() - WEEKDAY(TODAY()) + 8))
)
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.
[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.
Pure gold! Would never have thought to use USERSETTINGS this way. Thank you!
User | Count |
---|---|
38 | |
35 | |
27 | |
23 | |
18 |