I have an object in Salesforce where each record has a start and end date, typically 2-3 weeks apart, as well as a headcount. These events (records) often overlap and I need a way to show how many active individuals there are any given week (sum of active headcounts). Essentially, I want the x axis/category to be a series of dates (maybe each Monday’s date?) and the y axis would be the sum of the headcounts for the events that are active at that time, based on the x axis.
I’m thinking I’m out of luck, but any “Outside the box, inside the system” suggestions are appreciated! Thanks!
Question
Chart to Sum a field over a range of dates
+1
+9We had to do something similar with our MRP (Material Resource Planning) system that we built with Skuid. However, in your case it would be more of a HRP (Human Resource Planning)?? hehe.
Anyways, This is what the end result for us looks like for one row
You can use aggregate models to sum up the headcount with model conditions with greater than the start date and less than the end date. Then create a model to use for your table and have UI-Only Formula fields for each Week (This Week, Next Week, In 2 Weeks, Etc). The formula field uses the MODEL_LOOKUP function to pull in the data from the agg models into the table model.
e.g.
Agg Model A - Figure out how much you have next week
Aggregations: SUM(Headcount)
Condition 0
Field: Start Date
Operator: Is Greater than
Value: THIS_WEEK
Condition 1
Field: End Date
Operation IS Less than
Value: NEXT_N_WEEKS:2
This should give you a sum of the headcount for next week.
You then use the MODEL_LOOKUP, kind of like a VLOOKUP to pull in the data from the agg models into a model you can display as a table.
Translate
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.