Sum of total hours and wages based on time period

[Paulo]

Hello all!

I'm working on a timesheet tracker app to allow each employee can clock in and out, sometimes multiple times a day.
The app is working fine, but now I need to create a way to list each employee's total hours and salary, divided by month, so that I can make payments for each one of them and I'm completely lost. Someone could point me in the right direction please? I am attaching the table I am using for reference.
Any help will be greatly appreciated.

[Aurelien]

Hi @Paulo 

1) I see [Name] column is type Ref, I assume this is related to an employee table.

2) If you wish to have an aggregation per employee AND per month, then I would suggest creating a new table "month_aggregation" with columns:

• id
• Name
• month

and, in your Timesheet table, adding a virtual column [_month_aggregation], type Ref, source table month_aggregation, with expression:

ANY(
  FILTER("month_aggregation",
    AND(
      [Name]=[_THISROW].[Name],
      [month]=[_THISROW].[month]
    )
  )
)

3) In your table "month_aggregation", create a virtual column [_related_timesheet] with this expression:

FILTER("Timesheet",
  IN([_THISROW],[_month_aggregation])
)

4) in your table "Timesheet", add a virtual column [_total_hours], type Duration, with this expression:

[Date/Time In]-[Date/Time Out]

5) In your table "month_aggregation", create a virtual column [_total_hours], type Duration, with this expression:

[_related_timesheet][_total_hours]

[Paulo]

Thank you for your help, @Aurelien !
I created a new table "month_aggregation", but when I tried to create the virtual column [_month_aggregation] I get this error:

[Aurelien]

My bad! I forgot to tell you to add a virtual column [_month] in your table TimeSheet.

The expression would be:

MONTH([date/Time In])

And the expression would turn into:

ANY(
  FILTER("month_aggregation",
    AND(
      [Name]=[_THISROW].[Name],
      [month]=[_THISROW].[_month]
    )
  )
)

(just added an underscore before month)

For reference:

MONTH() - AppSheet Help

For the future, you may want to think about tracking the year as well.

[Paulo]

Sorry to bother you again, @Aurelien 
Now I got an error in 3)

---

@Aurelien wrote:

3) In your table "month_aggregation", create a virtual column [_related_timesheet] with this expression:

FILTER("Timesheet",
  IN([_THISROW],[_month_aggregation])
)

---

[Aurelien]

Hi @Paulo 

My mistake again ! 😅

Can you try:

FILTER("Timesheet",
  [_THISROW]=[_month_aggregation]
)

Equivalent to:
REF_ROWS("Timesheet",[_month_aggregation])

For reference:

REF_ROWS() - AppSheet Help

[Paulo]

I'm almost there, @Aurelien !
Now I got this error on step 5 🙏

[Paulo]

Sorry to bother you again @Aurelien 
I can't understand what I'm missing😓

[Aurelien · Accepted solution]

Hi @Paulo 

My bad again...I forgot to reply, thanks for raising.

Can you try this:

  SUM(
    [_related_timesheet][_total_hours]
  )

If you wish to have it under a decimal value, you can try:

TOTALHOURS(
  SUM(
    [_related_timesheet][_total_hours]
  )
)

For reference:

TOTALHOURS() - AppSheet Help

[Paulo]

Thank you so much @Aurelien !
Now I need to figure out a way to show all these information in a view😅