Solved: Date Range Filter

Matt_Stagg · 03-02-2020 12:45 AM

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?

Suvrutt_Gurjar

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.

View solution in original post

Suvrutt_Gurjar

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.

https://www.appsheet.com/samples/Allow-the-user-to-filter-a-view-based-on-a-form?appGuidString=91266...

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.

Matt_Stagg

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)

Suvrutt_Gurjar

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.

Matt_Stagg

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!

Suvrutt_Gurjar

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.

Arun_Kathpalia

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]))))

Suvrutt_Gurjar

@Arun_Kathpalia,

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.

Arun_Kathpalia

Thanks a lot @Suvrutt_Gurjar
I figured it out.

MEC

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!