Pivot table aggregation using AppSheet

[Wiktor_Jurek]

Hey folks,

I have two tables: employees, and tasks.

I basically want to have a view that summarises the capacities of each employee, by week, based on the tasks that they have assigned to them. This is very easily done using a pivot table in sheets

However, obviously, this isn’t the best idea as it offloads the logic and calculations to the spreadsheet, which isn’t best practice. I’d like to use a formula or some sort of system to ‘bulid’ a table that would be able to summarise week, person, and capacity.

Ultimately, even a table like this would be super useful to generate

Many thanks in advance, everyone.

[Suvrutt_Gurjar]

If the understanding of your requirement is correct, there may not be exactly the pivot table view that you desire but you could possibly take a look at group by Week , followed by Person in a table view and group aggregate for this table view could be SUM: Used Capacity or AVG: Order Capacity

In case of aggregate by SUM: the first group by of used capacity by weeks will be just arithmatic sum of all individual capacities.

help.appsheet.com

How to Control Grouping

You can display your data in sorted categories.

[Wiktor_Jurek]

Hi @Suvrutt_Gurjar

This is alright, but there are two significant caveats:

1. The [Week, Person, Capacity] table still needs to be built. I haven’t found a way to do this automatically without using Google Sheets formulas.
2. Grouping by two levels means the information isn’t available in a singular view. A user has to ‘click in’ to a week, and then they are greeted with the capacity per person. A core requirement is to have an all-encompassing view so we can see clearly what employees are working on a per-week basis, and how busy they are with it.

[Suvrutt_Gurjar]

Hi @Wiktor_Jurek
Yes, as mentioned, it will not be an exact pivot table. There will be some compromises.

Wiktor_Jurek:

The [Week, Person, Capacity] table still needs to be built. I haven’t found a way to do this automatically without using Google Sheets formulas.

Not sure why you mention this. If the Google sheet table “Tasks” is included in the AppSheet , I believe you could build a table view based on it with groupings as suggested.

[Suvrutt_Gurjar]

Another option , again not exactly like a pivot table will be

1. Create a slice on the Tasks table with an expression something like

[Tasks Table Key]= MINROW( “Tasks”, “_ROWNUMBER”, AND( [Person]=[_THISROW].[Person], [Week]=[_THISROW].[Week]))

2. If there are more than one tasks per person per week, you may need to add the “User Capacity” by person by week in another expression in a VC to create an [Aggregated Used Capcity by Person Per Week]

3. Display the slice in a table view with the column order as [Week], [Person] and [Aggregated Used Capcity by Person Per Week]

A point to note is that multirow expressions in VCs ( point 2 above ) could impact sync times. But Pivot by its very nature will involve some aggregation.