Track changes by DATE([Last Change])>=today()-7 or -30 0r -90 buuuuuuut cannot...

[Robert_Easter]

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!

[Suvrutt_Gurjar]

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

[Robert_Easter]

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

[Suvrutt_Gurjar]

You are welcome.

[AleksiAlkio]

Another solution is to add an LINKTOFILTEREDVIEW() that reads the number from the row and then opens that view but filtered.

[Robert_Easter]

@Suvrutt_Gurjar  & @AleksiAlkio  Sorta trying to do a combination of Both of your solutions.

So here is where I am at.
1. set up the Dashboard with both "Work Progression-View" A view of the Work Progression table  that the days are  incremental by 1
2. 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

 

[romulo_torres]

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

[Suvrutt_Gurjar · Accepted solution]

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.

[Robert_Easter]

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