Time duration

AppSheet AppSheet Q&A
1

I am trying to filter messages in my app for “last week”. this following error message really puzzles me.

Arithmetic expression ‘((TODAY()-[_THISROW].[Sent Date/Time]) < 7)’ does not have valid input types

[Sent Date/Time] is the date time type marks the time a message sent out. I am trying to build an expression in Slicing the messages table.

2

Hi @Tim_Mao

Welcome into the community !

The message indicates a difference into the inputs types.
TODAY() returns a Date type.
[Sent Date/Time], as you say, is a dateTime type.

both should be of either Date, or DateTime.

Two possibilities now:
use formula
((NOW()-[_THISROW].[Sent Date/Time]) < 7)
Or
((TODAY()-DATE([_THISROW].[Sent Date/Time])) < 7)

Let us know if that works for you

2 Likes
3

Hi,
TODAY () - “12/30/2001”: the Duration between the current Date and December 30, 2001 (a Date).
It returns the only duration Not the days. So follow the steps
HOUR (TODAY () - “12/30/2001”) /24<7.
HOUR (TODAY () - DATE([Sent Date/Time])) /24<7

3 Likes
4

@saravanamoorthi Good point, I was focused on the “not valid input types” message

@Tim_Mao In addition, have a look to this article that gives some examples in the section " Examples that Compute Durations in Days, Months, or Years"

2 Likes
5

Thanks for your help. I have tried and tidied up as the following. Still problematic.
AND(
OR(
CONTAINS([Recipients], USEREMAIL()),
CONTAINS([Sent By], USEREMAIL())
),
SWITCH(&FIlters[Period],“LAST WEEK”,HOUR(TODAY() - DATE([_THISROW].[Sent Date/Time]))/24 <7,“LAST MONTH”,HOUR(TODAY() - DATE([_THISROW].[Sent Date/Time]))/24<30, FALSE)
)

The expression intends to return all the messages either sent or received by me and in last week or last month. The selection criteria is recorded in a table named Filters and can be dynamically changed by user.

the error message:
Expression ‘AND( OR( CONTAINS([Recipients], USEREMAIL()), CONTAINS([Sent By], USEREMAIL() ), SWITCH(&FIlters[Period],“LAST WEEK”,HOUR(TODAY() - DATE([_THISROW].[Sent Date/Time]))/24 <7,“LAST MONTH”,HOUR(TODAY() - DATE([_THISROW].[Sent Date/Time]))/24<30, FALSE)) )’ could not be parsed due to exception: Sequence contains no elements.

What does this mean?

Tim

6

(Tips & trick : use some blank spaces, then check PreFormatted Text option, then BlockQuote option)

> AND( > OR(> CONTAINS([Recipients], USEREMAIL()), > CONTAINS([Sent By], USEREMAIL())> ), > SWITCH(&FIlters[Period],> “LAST WEEK”,> HOUR(TODAY() - DATE([_THISROW].[Sent Date/Time]))/24 <7,> “LAST MONTH”,> HOUR(TODAY() - DATE([_THISROW].[Sent Date/Time]))/24<30,> FALSE> )> )> >

What attracts my attention is your SWITCH input.
SWITCH formula can have only a value, and FIlters[Period] refers to a whole column, so will provide a list of values.

Can you try:
SWITCH(ANY(&FIlters[Period]),
instead ?
Assuming the Filters table is written with the & before, and that it contains only one row.

1 Like
7

Thanks! I just copied pasted to the Expression box. unfortunately, got the same error.
Expression ‘AND( OR( CONTAINS([Recipients], USEREMAIL()), CONTAINS([Sent By], USEREMAIL()) ), SWITCH(ANY(&FIlters[Period]), “LAST WEEK”, HOUR(TODAY() - DATE([_THISROW].[Sent Date/Time]))/24 <7, “LAST MONTH”, HOUR(TODAY() - DATE([_THISROW].[Sent Date/Time]))/24<30, FALSE ) )’ could not be parsed due to exception: Sequence contains no elements.

I can not understand what the sentence “Sequence contains no elements” means?

you are right, the filters table only has ONE row/record, which can be edited by user realtime

8

The & in &Filters[Period] is likely the problem. Why is the & there?

1 Like
9

without &, it gives an error as incorrect input to SWITCH

10

Big thanks! I removed & and replaced it as TEXT(). problem Solved!! looks the column of Period is set as ENUM so needs TEXT() function to convert.

1 Like
11 
AND(
  OR(
    IN(USEREMAIL(), [Recipients]),
    IN(USEREMAIL(), [Sent By])
  ),
  SWITCH(
    ANY(Filters[Period]),
    “LAST WEEK”,
      ((HOUR(TODAY() - DATE([Sent Date/Time])) / 24) < 7),
    “LAST MONTH”,
      ((HOUR(TODAY() - DATE([Sent Date/Time])) / 24) < 30),
    FALSE
  )
)
2 Likes
12

It works!! but I noticed that you moved [_THISROW]. the [Sent Date/Time] is the column which I assume contains multiple values. It looks Appsheet will automatically decides the work is Row by Row. If this is the case, I still learnt that many other cases still see [_THISROW], then how to decide where to use [_THISROW]?

2 Likes
13

Use [_THISROW] only within FILTER(), LOOKUP(), MAXROW(), MINROW(), and SELECT() expressions to refer to the row from which the expression was launched.

See also:

[FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT()](https://community.appsheet.com/t/faq-filter-lookup-maxrow-minrow-and-select/24216/43) Tips & Tricks ?

Back-references within nested queries ([_THISROW-n]) Consider a complex App formula with “nested” SELECT() expressions (SELECT() expressions within SELECT() expressions): SELECT( table[column1], … SELECT( table[column2], … SELECT( table[column3], … ) … ) … ) Let’s call the row for which this entire expression is evaluated as its App formula the Origin Row. Within this entire expression, we can refer to column values of the Origin Row by dere…

2 Likes