SUM(MAX() over (partition by))

lb1234 Member
edited February 23 in Magic ETL

For this dataset used, I have a list of appointments scheduled, there are closers tied to individual appointments and the closer's weekly scheduled appointment limit. So, where the user is showing for multiple appointments, their weekly scheduled appointment limit is repeated on those lines.

The card I'm trying to create is summing up the teams total scheduled appointments for the week and subtracting that from their closer's cumulative max(weekly appointment limit.) This is the beastmode I thought would work but unfortunately, I'm having issues:

SUM(MAX(`Closer.WeeklyCloseApptLimit`) OVER (PARTITION BY CLOSER.ID)) - COUNT(DISTINCT `Opportunity ID`)

Any suggestions?