Grouping rows with sum duration virtual column

[Espegg]

Hello,
I have already created in a table view mode like the table on top of the example screenshot with the details of date and TOTAL Time cumulated on day.

Now I need, always in table view mode like the table on bottom of the example screenshot, grouping by:
- Company
- User code
- User
- Course
- Time of the course
(Date field not more visible)
and create a virtual column to sum TOTAL Time cumulated by a User.

How could I proceed?

[WillowMobileSys]

There are a few approaches....

First, AppSheet does not have an ability to create a view like that you have shown.  However, there are Grouping and Aggregate capabilities inside of the Table views but they are simplistic.  A great benefit of these views are that they INCLUDE the detail data that you can drill into to see which rows make up the aggregate value.  Below are a couple examples of the same Grouped data shown in two different ways.  If this format of the data is acceptable to you, it is by far the easiest approach.

Second, You can build a table view to match exactly that you have shown.  Use actions/automation to add and update rows in your summary table upon any new row added or existing row updated.  It takes time to build this but the benefits are you have a table view exactly like you want and calculations on happen when needed.

Third, A trick that some use is to add a Virtual Column to your data table that computes the "TOTAL Time Cumulated" value.  It would appear on every row - the rows in each group would have duplicate values.  Then create a FILTERED view selecting a single "representative" row from each grouping you wish to display.  This can get tricky in creating the proper expression to select those "representative" rows.  Another downside is that the Virtual column calculation is applied to every row on every Sync.  If you have a lot of these type of VC's and/or the table grows very large, Sync can be significantly impacted.

Table Views with Grouping and Aggregate functions applied

[Marc_Dillon]

I'd suggest a pivot table or QUERY() function on a new tab in your gsheet.

[Espegg · Accepted solution]

Hello,
I have solved creating a new slice and with row filter condition:
[_THISROW] = INDEX( FILTER( Foglio1 , [User Code] = [_THISROW].[User Code] ) , 1 )
and then grouping by Company.

Anyway, thanks for your support!

Great!

[amnhint]

 I've been looking for this for ages. Your response is a huge help. Thanks a million!  💋