I know, I know!!
I should be able to figure this out easily! First the goal.
GOAL: one place to change the look-back periods of changes in the ‘Circuits Path’ Table
Each entry has various columns that can change and I want to be able to note the most recent changes by Timestamp in the [Last Change] column. That is all set up.
Additionally, I have set up 4 Track changes VIEW’s of my ‘Circuits Path’ Table using the formula DATE([Last Change])>=today()-7 . There are 4 separate slices, each with the same formula but a different look back amount of days
DATE([Last Change])>=today()-7
DATE([Last Change])>=today()-30
DATE([Last Change])>=today()-90
DATE([Last Change])>=today()-120
Each slice works great!
What I want is 1 “menu” view that allows me to choose between the various intervals of look-back periods ( &days, 30 days, 90 Days 120 days (or maybe even a user-defined set of days) that then shows me the rows where the [Last Change] timestamps are those ranges.
I can place each as a separate menu Items are fine BUT that takes up too much real estate and I am having trouble creating a solution to build all the options into one single view.
I created a separate table Called ‘Work Progression’ Table and added a view in the menu with a row for each desired look-back date, But I can’t figure how to get the Row in the tables to activate the Views from it
I know this is likely an obvious solution
but I am just plain stuck… Help!
In general, you could use a user based filtering technique such as demonstrated in the following sample app at at the help article Get started by using the sample apps - AppSheet Help
Slice based on user input - AppSheet
The sample app lets the use to select colors to display in the chart. You could use a number type column that allows the user to select any number of days.
Also the sample app is for a single user. In a multi user environment, you would need to store user specific selection in say a Users table, where there is one row for each user to store the user specific selection of number of days.
Slice based on user input - AppSheet
1 Like
Thank you for your response,
I am currently looking over this app you have shared as an example. I believe the underlying theory of this will work in my case. Will get back to you when I have solved a few oddities
1 Like
Another solution is to add an LINKTOFILTEREDVIEW() that reads the number from the row and then opens that view but filtered.
@Suvrutt_Gurjar & @AleksiAlkio Sorta trying to do a combination of Both of your solutions.
So here is where I am at.
- set up the Dashboard with both “Work Progression-View” A view of the Work Progression table that the days are incremental by 1
- I can hopefully pass the increment of days chosen by the user, whether 1 or 7 or 2 etc., to the “Changes” Slice which uses a True/False function to evaluate the function passed.
The formula I am testing with Works with a number statically inserted… in the Slices True/False formula: DATE([Last Change])>= today()-1
But When I change the formula I am using to try and pass the variable to change the "today() -1 to another number is coming back an error that formula is: DATE([Last Change])>= today()-Work Progression[Days]
Work Progression[Days] being the table[column] the Dashboard view sends and stores the user input from the dashboard to hopefully pass the increment of days to the Filter/slice view Changes.
Example 1 Works
Example 2 Does not work and causes the error
The Table is structured simply like this
Help with this would be appreciated,
Thanks
Hi, i made a simulation and i got your error, and i got a “green check” encapsulating with the function NUMBER()
Something like this in your case.
DATE([Last Change]) >= today() - NUMBER(Work Progression[Days]).
test the result and good luck. Hope it helps!
https://support.google.com/appsheet/answer/10108120?hl=en
1 Like
Please try with a slice filter expression of
DATE([Last Change])>= today()-ANY(Work Progression[Days])
The above expression assumes that in the Work Progression table, there is just one row where only one app user is making the changes and that the Work Progression- View is a detail view based on that row.
In a multi user environment as earlier mentioned, there will need to be one row each for each user to store the [Days] vallues of each user and the slice expression will also accordingly change to fetch the [Days] value of the logged in user from her/his respective row.
1 Like
OH My goodness the ANY function! And it was in the slice in the App you had pointed me toward .. Thank you Both for the help Works Brilliantly
1 Like
