Looking to filter chart data by user defined "from" and "to" dates

[Agent5D]

Hi!

I'd like to create a dashboard that displays a chart with all data, and have the option for a user to filter the chart results based on a from and to date.  Something like this: 

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

But with dates instead of colors. Is this possible?  Thanks

[graham_howe · Accepted solution]

You just need to convert your statement into something that evaluates as True/False, like this:

AND(
  [Timestamp] >= Chart Date[From Date],
  [Timestamp] <= Chart Date[To Date]
)

This filter will evaluate to True for any rows with a timestamp within the range.

[Suvrutt_Gurjar]

Yes it is possible. Instead of "Color" fields you could have "Date" fields. You will need two fields in the Filter table "From Date" and "To Date"

You can then tweak the slice expression to include both "From Date" and "To Date"

[Agent5D]

Thank you soo much Suvrutt/ I will give it a shot!  

[Agent5D]

Ok so I tried for aa couple hours (learning) to make that exaple wok based on creating a slice to filter rows by a user defined from - to dynamic date range and kept getting code validation errors.  It turns out that Row filter for slices seems to rely on a Boolean result (Yes/No) like sample template, red or blue, and does not appear to handle date range queries.  Any other ideas are greatly appreciated. This seems like it should be simple to do, I just can't find how on my 2nd day of appsheet designing. I appreciate any and all help with this.

[Suvrutt_Gurjar]

Sure.

If you share what slice expression you have tried and what your filter and data table columns look like , we could help better.

[Agent5D]

Thank you.  So here is what I am working with...

Table 1 - Data...

Table 2 - Selection

I have been following the model here to setup a view table filtered by a dynamic user defined Date range... https://www.appsheet.com/templates/Allow-the-user-to-filter-a-view-based-on-a-form?appGuidString=912...

I tried piecing together the slice filter critera, with something like: 
[Timestamp] >=Chart Date[From Date] And [Timestamp] =<Chart Date[To Date].
Besides my expression probably being incorrect, the core problem seems to be that the slice Row filter conditions rely on a True/false expression that checks if a row should be included in the slice, vs a sql type date range selection that I am used to.

[graham_howe · Accepted solution]

You just need to convert your statement into something that evaluates as True/False, like this:

AND(
  [Timestamp] >= Chart Date[From Date],
  [Timestamp] <= Chart Date[To Date]
)

This filter will evaluate to True for any rows with a timestamp within the range.

[Agent5D]

Thanks so much for chiming in Graham!. I just gave it a whirl and received this:

As you can see from my table screenshots, the Datetime format is applied to all 3 columns, so not sure what is causing this.

[graham_howe]

Yeah sorry, I wasn't thinking. Even though the Chart Date table only has one row, it is still considered a list. I think the revision I posted below should work.

[Agent5D]

With Graham's help I finally got this working.  The statement above did the trick, I just had to build my dashboard items based off the resulting slice. The dashboard components weren't based off of static views and not the slice data. This is because I never had a slice to work with until Graham came along and helped 😉 Now that I have gone through this, everything makes perfect sense, although it would have taken me forever to come up with   

AND(
  [Timestamp] >= Chart Date[From Date],
  [Timestamp] <= Chart Date[To Date]
)

A million thanks Graham!!!

[graham_howe · Accepted solution]

That's great news, I just messaged you a final test which actually would have probably uncovered this. In case others are interested, as well as using the Test link when building an expression to see how the rows will be treated, it is also a good idea to hit the View Data link from the top of the Slice page when building a slice. If we had done this earlier it would have shown the slice to be working correctly which would then indicate that it must not have been accessed by the other views.

[Juanjoge]

And where do you put this instruction and define the start and end date?

[Agent5D]

So, I just ran into this post https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Date-Range-Filter/m-p/315465#M90818

and decided to try this expression on a slice based on my Entry Log Table: 

Seems like I am getting closer.

[graham_howe]

I was thinking about this a little more and although I'm fairly confident the expression I posted (after I just corrected the typo) should work, it looks like there is some redundancy in it. I feel that a modified version of what I posted originally should work fine

AND(
  [Timestamp] >= INDEX(Chart Date[From Date], 1),
  [Timestamp] <= INDEX(Chart Date[To Date], 1)
)

[Agent5D]

This seems to work as well and is much cleaner.  However, with 1 valid from / to date record in Chart data table, the data still does not filter out the dates when I click on the test button.

[graham_howe]

OK, let's do a little investigation here, can you try hard coding a date range in an expression and testing to see if the rows are filtered at all, something like this:

AND(
  [Timestamp] >= DATETIME("1/1/2022 00:00"),
  [Timestamp] <= DATETIME("1/21/2022 23:00")
)

If this doesn't filter then it means either my logic is wrong, or somehow the [Timestamp] column is not containing the data we think it is. Of this does work then we can look at the Chart Date table.

[Agent5D]

I don't know why I didn't think of that Graham. You logic is solid, yet still not filtering:

I really don't know why!

[graham_howe]

I just want to make sure I understand that last example. Are you saying that although using the hard coded date range did correctly flag the rows in the test page, that same expression did not filter the rows? 

Or are you saying that hard coded dates worked and my original expression with the indexes does not work?

[Agent5D]

Apologies for being unclear. I was saying that even though the editor like your expression code (both hardcoded and not), the records are still not filtering by date on the test page or anywhere, even with hardcode.

In my screen captures above, you will see that, even though I selected a date range from 1/25/22 - To 1/26/22, All records are still showing on test page, including the one you can see on the snap from 1/22/2022.

In my mind, this should be working. Any idea why the records won't filter on the test page, even with your amazing and efficient expression code?  

[graham_howe]

I tried sending a private message but I don't know if you got that. The first snap shot you show is from the expression test and does prove that the expression is flagging some rows with N (false) and others with Y (true) so that exact expression is being used in a slice filter then I don't know why it would not have the same effect. Can you just show a screen shot of the slice design?

[Agent5D]

Thanks for reaching out Graham.  I just saw your PM and replied!

[graham_howe]

Almost there I think, try this

IN(
  [Timestamp], 
  SELECT(
    Entry Log[Timestamp],
    AND(
      [Timestamp] >= ANY(Chart Date[From Date]),
      [Timestamp] <= ANY(Chart Date[To Date])
    )
  )
)

[Agent5D]

Thank you.  It didn't error out, and it seems like the data is not filtering, Here were my next steps:

1. I created a Form view for the Chart Date filter table to edit the from-to dates.
2. I tested it to make sure that it updates the Chart Date Table and it does create a NEW row on each save (Having more than 1 from-to date record seems potentially problematic???)
3. Then, I created a chart view of the data slice based on Entry Log Data Table, that shows a histogram of AVERAGE of Evaluation numbers and a Results table view for testing.
4. Lastly, I created a dashboard view. I was able to include the chart and table view, but not my Date input form, so for testing, I just opened up my Date input form separately and made an entry.

I made sure I had a valid from /to dates on only1 row in my Chart Date Table on the back end, and everything looed good. However, the chart and results table are not being filtered in the dashboard \, and without any errors?

I know that is a lot. Any Ideas? Sooooo close.

[gregdiana1]

Perusing this issue and from experience, I see the same issues I had and which are not well documented and all relate to the date format used. To sho wthis I created 4 different timestamps using different formats as shown below in google sheets

The first uses the same format used here. The second is the same format but converted to number and as may be seen there is no change. The format used is m/d/yyyy h:m:ss

The third is the normal format used by most SQL databases namely  yyyy-mm-dd HH:mm:ss

and the fourth is the same as the third but converted to a decimal number and as may be seen it returns the actual DateTime as a decimal number which is the true underlying number representation.

What this shows that at the surface level the format used appears normal but when used in queries the format prevents the underlying number from being used and rather an unknown text date is used.

This does not apply when using the format I have shown as the underlying decimal number date representation is readily available.

This seems silly but it can cause one to tear one's hair out simply because APPSHEET conforms to the normal database type number and ISO versions of the date and this is neither well-known nor documented and what led to the head bashing exercise above and a seeming solution.

Now creating a virtual column and doing and using the same formula I get

AND ([timestamp] >= DATETIME("1/25/2022 00:00"), [timestamp] <= DATETIME("1/26/2022 23:00"))

AND ([timestamp] >= DATETIME("1/25/2022 00:00"), [timestamp] <= DATETIME("1/26/2022 23:00"))

and this works for all time stamps but not for timestamp2 which is decimal and gives the following error

In your case you are using dates obtained from another source whereas the above used the DATETIME FUNCTION to correctly convert the used format to the correct internal time that APSHEET understands.

This suggests that your From Date and To-Date format is incorrect.

You should ensure that the format of Timestamp, From_Date and To_Date are identical both at spreadsheet and APPSHEET column type as APPSHEET uses its date representation which is normally ISO-8601.

Maybe APPSHEET should document what DateTime standard it uses?

The bottom line is that when using dates for filters and queries make sure the database or spreadsheet DateTime format is compatible otherwise one gets greek or rubbish in and gives rubbish out. 

If you want a challenge try format week of year.

[Steve]

Apps using Date, Time, and DateTime formats - AppSheet Help

Date and time expressions - AppSheet Help

[gregdiana1]

Thanks, Steve, but this is a function call and not a week-of-year format like Www.

So how does one get the week of the year from TEXT(when, format) ?

what format does / can one use ?

String Meaning Example (en_US locale)

d	Day	24
E	Day of week	Thu
EEEE	Day of week	Thursday
LLL	Standalone month	Aug
LLLL	Standalone month	August
M	Month	8
Md	Month and day	8/24
MEd	Month, day, and day of week	Thu, 8/24
MMM	Month	Aug
MMMd	Month and day	Aug 24
MMMEd	Month, day, and day of week	Thu, Aug 24
MMMM	Month	August
MMMMd	Month and day	August 24
MMMMEEEEd	Month, day, and day of week	Thursday, August 24
QQQ	Quarter	Q3
QQQQ	Quarter	3rd quarter
y	Year	2015
yM	Year and month	8/2017
yMd	Year, month, and day	8/1/2017
yMEd	Year, month, day, and day of week	Thu, 8/24/2017
yMMM	Year and month	Aug 2017
yMMMd	Year, month, and day	August 1, 2017
yMMMEd	Year, month, day, and day of week	Thu, Aug 24, 2017
yMMMM	Year and month	August 2017
yMMMMd	Year, month, and day	August 24, 2017
yMMMMEEEEd	Year, month, day, and day of week	Thursday, August 24, 2017
yQQQ	Year and quarter	Q3 2017
yQQQQ	Year and quarter	3rd quarter 2017
H	Hour in day (24-hour time)	15
Hm	Hour and minute (24-hour time)	15:38
Hms	Hour, minute, and second (24-hour time)	15:38:00
j	Hour (12-hour time)	3 PM
jm	Hour and minute (12-hour time)	3:38 PM
jms	Hour, minute, and second (12-hour time)	3:38:00 PM
m	Minute	38
ms	Minute and second	38:00
s	Second	0

etc.

[Steve]

TEXT() - AppSheet Help

[gregdiana1]

TEXT() has no WOY format only via the function you noted earlier.