Question regarding pivot table using a view with repeating values in some columns

[zedbee]

Hello,

I would like to create a pivot table for data similar to the table below. I'm using a view to create the pivot table. The view joins the PO table and SO table, and that's causing the PO ID rows to be repeated.

PO Qty total should calculate the sum of  qty of unique PO IDs. I was thinking of using the custom totals/aggregations formula to calculate it, but I'm getting stuck.. 

Any help on this would really be appreciated!

Product	PO ID	PO Qty	SO ID	SO Qty
Product A	POA1	5	SOA1	1
Product A	POA1	5	SOA2	2
Product A	POA1	5	SOA3	1
Product A	POA2	10	SOA4	3
Product A	POA2	10	SOA5	2
Product A	POA2	10	SOA6	1

PO Qty Total: 15 (unique PO ID);  SO Qty Total:  10

Thank you!

[CoopperBackpack]

Hello @zedbee,

My only idea is to use a custom formula. If this works for you, you may add Totals&Aggregations, select Custom, and use the SQL statement that sums the PO_Qty grouped by PO_ID.

Please note that when you select from the View, you need to add the _v_ prefix before the View name.

[Aether]

 Hi! Just to add, Caspio has released a video tutorial about summarizing data with Pivot:

I hope this help