Issue in total/subtotal of the pivot

M_A

SEC_TO_TIME(SUM((End Time - Start Time)) - 3600)

I am using the above calculation to get the employees who has exceeded their break by 1 hour. it is working fine in normal condition but when i put this into pivot and checking the subtotal / total, it is just subtracting 1 hour from the total which is wrong. it should just give the total which is like 0:42:00 in the below example.

Supervisor	Employee Name	BE V2	Duration
AB	X	0:02:00	1:02:00
AB	Y	0:29:00	1:29:00
AB	Z	0:11:00	1:11:00
AB Total		2:42:00	3:42:00

GrantSmith

This is because how your Beast Mode is written, it aggregates everything first and then just subtracts an hour.

What you'll want is (assuming you're filtering for records where the duration is already > 1 hour:

SEC_TO_TIME(SUM(`End Time` - `Start Time` - 3600))