Filtering date-range with numeric "slider"-input

hi,

i want to filter “date/time”-range" of a “slice” with “slider”-Input (numeric value)

e.g. date-range = the last 30 days (slider = 30)
date-range = the last 7 days (slider = 7)

step 1: adding a “vc” with formula for calulation number of days from “Today”
HOUR (TODAY() - DATE([Date/Time])) / 24
table-name: “weight”
vc-column-name: “days”
no problem!

step 2: generating numbers between 1 - 30 with slider in a separate table (only one row with value of slider-input)
table-name= “select”
column-name = “days”
no problem!

step 3:
filtering “slice” with “slider-input”

setting up “row-filter-condition” of “slice”: [weight].[days] >= [select].[days]

this does not work because i cannot select table “[select].[days].” for “row-filter-condition”.

how can i achieve this? - many thanks in advance for your help!

Bsed on understanding of your requirement, you may wish to note and try the following

In general, you may wish to avoid naming tables and columns same as those of AppSheet functions. There is table name Select and column name as days. AppSheet has functions SELECT() and DAY(). So a SELECT() on select table with days column would look like SELECT(Select[days], DAY([Date])…)
It can be confusing to debug and at times may throw error as it could clash with Appsheet reserved words. AppSheet is very generous with element name selection and allows most such words, but better to avoid if possible.

So renaming the step 2 table as “SliderInput” in place of “select” and [days] as [SliderDays] ( assumed to be number type column) , you could try the below slice expression

A. If there is just one data row in the “SliderInput” table

DATE([DATE/TIME] >= TODAY() - ANY( SliderInput[SliderDays])

B. If there are multiple rows in the “SliderInput” table, say one for each user, you may need to use a SELECT() statement with suitable argument as shown below

DATE([DATE/TIME] >= TODAY() -ANY(SELECT( SliderInput[SliderDays], [[Email]=USEREMAIL()))

The expression may need minor modifications based on your exact requirement. The above will return the rows with dates in the past those many days from today as set on the slider.

Edit: Added any to the second expression.

1 Like

hello Suvrutt_Gurjar,

i can learn a lot from from your explanation:
a) naming “tables” and “columns”
b) function “any()” …

i will test it out - thank you so much !!!

2 Likes

You are welcome. Please take a look at ANY() function article just in case you have not

In fact your mention of ANY() reminded me that there was ANY() required in the second suggested expression as well. :slight_smile: Have edited the first post accordingly.

1 Like