I’m running into a limitation and wondering how others are handling this (or if I’m missing something obvious).
The setup: I have a table with three fields: sourced, taken, and load_date. Easy enough to create a formula field for variance (sourced - taken).
What I need: I need to calculate percent variance as (SUM(variance) / SUM(sourced)) * 100 but only across records that fall within a user-selected date range. Basically, the user filters the collection view by load_date and I need the aggregated percent variance to reflect only those filtered records.
The problem: Rollup fields calculate against all linked records regardless of what’s displayed in the filtered view. I’ve tried creating a rollup table with pre-defined periods (this year vs last year, etc.) but that feels like a poor solution when you start thinking about every month/year combination. The rollups don’t respect UI filters, they’re static calculations.
I understand formula fields work at the record level and rollups work against linked records, but is there any pattern people are using to get true dynamic aggregation based on user-applied filters?
Anyone found a clean solution for this? Or is this just a current gap in the platform?