calculate the sum of averages across multiple records that have multiple variables in common

Hi All,

Not sure if this is possible, but wanted to ask. I'm trying to calculate the sum of averages across multiple records that have multiple variables in common.

Example: I have a set of 6 Salesforce records and each has a numerical value for field A. I want to create a table that first takes the average of field A of records with the same value for picklist fields B, C and D, and then sums the calculated average(s) based on having the same value for picklist fields B and C.

Example: model object = Race Results, fields for each record in quotes below.

RR Record 1:
"Event" = City
"Location" = Park
"Class"= Special
"Place" = 50
RR Record 2:
"Event" = City
"Location" = Park
"Class"= Special
"Place" = 100
RR Record 3:
"Event" = City
"Location" = Park
"Class"= Extra
"Place" = 200
RR Record 4:
"Event" = City
"Location" = Park
"Class"= Extra
"Place" = 300
RR Record 5:
"Event" = Village
"Location" = Upper
"Class"= Special
"Place" = 25
RR Record 6:
"Event" = Village
"Location" = Upper
"Class"= Special
"Place" = 50

In this case, I would want to see a final table like the attached. The calculation would be to take the average of "Place" for records that have "Event", "Location" and "Class" in common, and then sum those averages for records that have just "Event" and "Location" in common. So, SUM(AVG(RR1Place, RR2Place),AVG(RR3 Place, RR4 Place)) would give you 325 for the first value, and the second value would be 37.5 = SUM(AVG(RR5 Place, RR6 Place)).

I already have a basic model that is pulling the correct data from Salesforce, and have been able to create a table from it that displays all of the records, but I am unsure how to move forward from here in order to create a second table that shows the above calculations. I've been playing around with using an aggregate model, field groupings, formula fields, etc. but am not having much luck/ not sure if it's possible within the Skuid UI.

Thank you!
Sarah
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!