The difference between 2 SUM formulas

AppSheet AppSheet Q&A
1

Can anyone tell me the difference between these 2 formulas, apart from 1 is supposed to calculate break hours from today, and the other since Monday. The problem is the bottom one doesnt take the [Task]=“Break” into consideration?

SUM(
  SELECT(
  (Timesheet[Hours],[Task]="Break"),
    AND(
      (LOOKUP(USEREMAIL(), Staff, Email, Full Name)= [Name]),
      ([date] = TODAY())
    )
  )
)

SUM(
  SELECT(
  (Timesheet[Hours],[Task]="Break"),
    AND(
      (LOOKUP(USEREMAIL(), Staff, Email, Full Name)= [Name]),
      ([date] >= TODAY() - (WEEKDAY(TODAY()) - 2))
    )
  )
)
2

How do you know it is not being taken into account? I’d rather review the AND() part.

1 Like
3

Because the result displays ALL hours since Monday, also the expression assistant does not mention [Task]=“Break”, how would you write it?

jonbowles84_0-1644923649302.png
jonbowles84_0-1644923649302.png1311×873 48.8 KB

1 Like
4

I see, thank you!

In the screen capture I noticed extra pairs of parentheses that I haven’t noticed in your reply, which would prevent the SELECT statement from functioning correctly.

Also, SELECT() should have only two arguments with the latter returning a TRUE/FALSE value, while the third is optional to specify whether you would like to have unique records returned.

Therefore you should modify both of your expressions as follows:

  • First expression:
    SUM(
    SELECT(
    Timesheet[Hours], AND(
    [Task]=“Break”,
    LOOKUP(USEREMAIL(), Staff, Email, Full Name)= [Name],
    [date] = TODAY()
    )
    )
    )

  • Second expression:
    SUM(
    SELECT(
    Timesheet[Hours], AND(
    [Task]=“Break”,
    LOOKUP(USEREMAIL(), Staff, Email, Full Name)= [Name],
    [date] >= TODAY() - (WEEKDAY(TODAY()) - 2)
    )
    )
    )

One last thing, I believe you don’t have to use a LOOKUP statement here, since you are not interested in the returned value but merely needs to have the records of the current user. If you tell me in which table are these expressions I could be able to give you a simpler more efficient alternative. Thanks.

5

Thanks, they are in the “Timesheet” table, and the LOOKUP is referencing the “Staff” table.

Im thinkling now, I might go 1 further and have an [hours] - [hours] where [task]=“break”

6

And how do you fill the “Name” column in “Staff” table please?

You can go as far as you want :slightly_smiling_face:

7

its concatenated [First name] & [Last Name]

8

I’m sorry, I wanted to ask you how do you fill it in the “Timesheet” table (not Staff). If it is concatenated, where do you get the concatenated values from?

9

Ahh, its pulled from the “Staff” table where USEREMAIL()=[email]

10

Here you have it :slightly_smiling_face: and you should really avoid the use of LOOKUP() unless necessary, especially when inside a SELECT(); for both are expensive statements.

Ideally, your Staff table should have its key column as “email”, while your Timesheet table should have a ref column to Staff containing the staff email, instead of the “name” column. It is possible that you’ve included a name column in Timesheet for display purposes, but in this case it is sufficient to set the “Name” column in “Staff” as Label. In this way, any reference to Staff will show the “Name” instead of Staff’s key column.

Thus, your lookup statement could be replaced by:

USEREMAIL () = [email]

You can optimize even further; since your SELECT() statements will keep getting slower by the day as your dataset grows. Therefore, it is better to remove them completely and use slices instead.

First slice “breaksToday” will have its row filter condition as:

AND([Task] = “Break”, USEREMAIL() = [email], [date] = TODAY())

The second slice “breaksThisWeek” will have:

AND([Task] = “Break”, USEREMAIL() = [email],
[[date] >= TODAY() - (WEEKDAY(TODAY()) - 2)
)

Then when you need to sum the corresponding hours you’ll just need to use:

SUM(breaksToday[Hours])

and:

SUM(breaksThisWeek[Hours])

1 Like