Weekly hours by Customer ID

[AI-FL]

I have columns Customer ID, Job ID, Job Date, Job duration. I also calculated a column for which WEEKNUM the job falls in. How can I pull out the total hours for all jobs each week by Customer ID?

[Marc_Dillon]

help.appsheet.com

SUM()

Arithmetic sum of list items

help.appsheet.com

SELECT()

Gather column values from row in table or slice

help.appsheet.com

Date and Time Expressions

Incorporate dates and times into the behavior of your app.

[AI-FL]

That works. Here’s what I used for each week.
=QUERY(Jobs!1:1000, “SELECT B,C,D,J,K, SUM(L) WHERE G = 37.00 GROUP BY B,C,D,J,K”,1)

[Rafael_ANEIC-PY]

That looks to me like an sheets or excel formula, you might want to try to keep most of the operations inside of your app, since it can affect performance, especially if you have complex formulas for each row on lenghty tables, that can absolutely murder your sync times.

But if you’re gonna go with that, let me give you a couple tips:

If you’re gonna have a formula for each row, always start with a IF(A1<>"",(your formula),) so that the sheet checks if it is a blank row first before it calculates anything else, this can really save you a loooot of performance issues.

If there are a lot of jobs then eventually 1:1000 won’t be enough, i suggest using something like Jobs!A:D for example, that will take into consideration new rows.

[AI-FL]

You’re right. I started directly in the sheet to see if it worked but appsheet does not like QUERY. Is this formula possible, maybe using a slice?

[Rafael_ANEIC-PY]

Of course, @Marc_Dillon linked a few interesting articles, i recommend you start there, after that you should have an idea of how to put together your expression or at least with which function to start with, we’ll be here to answer any specific questions.

Or if you need help getting the exact expression, it is often required that you share more information about your app structure, like tables, columns, references, etc.