Time duration

Tim_Mao · 04-11-2021 09:31 PM

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.

Aurelien

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

saravanamoorthi

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

Aurelien

@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"

Tim_Mao

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

Aurelien

(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.

Tim_Mao

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

Steve

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

Tim_Mao

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.

Steve

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

Tim_Mao

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

Steve

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