Gday Team,
Iโve got an app Iโm building at the moment for a respite center, and weโre wanting to filter graph results based on user input for a start date & end date.
Currently, weโve got one column [Date] which is the date the entry was made. weโve tried a few methods using the user input data template, but we canโt seem to find a way of filtering based on user-defined dates.
Ideally, weโd like to have just the one [Date] column which users can filter a beginning & an end date from the one column.
Is this something anyone has experience with?
Solved! Go to Solution.
Hi @Matt_Stagg,
Assuming that just like the sample app , your filter table currently has only one row for user setings , please explore an expression something like below in slice filter
IN( [Date], SELECT(Activities Outings [Date], AND( [Date]>=ANY(Date Filter[Start Date]), [Date] <=ANY(Date Filter[End Date]))))
Here Date Filter is the name of the filter table.
Hi @Matt_Stagg,
Welcome to AppSheet community.
I believe you have couple of options based on how frequently you need to filter. Please take a look at the below sample app. If you have many uswers who need to filter the records based on dates, then you may need to define a user settings table to retain each userโs setting such that each user has one row of his own to retain his filter selection. The sample app has just one row for user input.
if you have infrequent requirement to filter the records, then I believe you may wish to consider the usersettings concept. However typically it may be used for less frequently disturbded user specific settings in the app.
Gโday suvrutt, thanks for the response!
Yes Iโm familiar with this based on user input - Iโve mimicked the design to an extent but Iโm still struggling a little (hopefully you can help!)
Iโve got a table (Activities Outings) which collects date in a single column, and another table (date filter) which has three columns (ID, start date, end date)
Iโve been able to develop a slice based on user input:
ANY(Activities Outings [Date]) = [Start Date]
which seems to be half the battleโฆ This filters the start date based on user input, but I have tried a number of different approaches to filter results between a start date and an end date. No idea what Iโve been missing using a number of filters (FILTER, ANY, AND)
Hi @Matt_Stagg,
Assuming that just like the sample app , your filter table currently has only one row for user setings , please explore an expression something like below in slice filter
IN( [Date], SELECT(Activities Outings [Date], AND( [Date]>=ANY(Date Filter[Start Date]), [Date] <=ANY(Date Filter[End Date]))))
Here Date Filter is the name of the filter table.
Ah that worked perfectly!
Thanks for offering the solution and letting me be a part of the community too. I hadnโt found the IN function anywhere - going to spend some time studying this function now.
Again, thank you very much!
You are welcome @Matt_Stagg
AppSheet has rich help documentation. You will find various help articles at
https://www.appsheet.com/Support?q=&hPP=10&idx=help&p=0&is_v=1
For example if you search for Expressions, you will get various expression groups List, Math, Yes/No expressions and so on. You can even refer to articles explaining individual expression function(s). For example please search for CONTAINS() to know how CONTAINS() function works. For some reason , the IN() function does not show up in search even though there is an article explaing IN() as well.
Hi @Matt_Stagg @Suvrutt_Gurjar,
Where do we need to enter this formula to get the desired results?
IN( [Date], SELECT(Activities Outings [Date], AND( [Date]>=ANY( Date Filter [Start Date]), [Date] <=ANY( Date Filter [End Date]))))
Welcome to AppSheet community.
The suggested formula is for a slice filter expression to filter rows. The expression in particular is with reference to the table and columns structure/ names mentioned by @Matt_Stagg in his app in the post above. In general the discussion is with respect to the following sample app.
Just in case you have not , you may wish to take a look at the concept of slices in AppSheet in the below mentioned articles.
Thanks a lot @Suvrutt_Gurjar
I figured it out.
Hi @Suvrutt_Gurjar,
I came across this while searching for answers on how I can filter data based on a user entered date being less than the maturity date. This is for a loan app that Iโm developing. Hereโs the query that I wrote to give you an idea.
Iโm not sure if itโs possible, if you could tell me itโd end my search for answers.
Thank you in advance!
User | Count |
---|---|
40 | |
35 | |
30 | |
23 | |
17 |