Skip to main content
Nintex Community Menu Bar

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

Hey @Smdrich


  • , it seems like there are a few ways you may be able to accomplish this in Skuid depending on what exactly you want (e.g. how specific your criteria are and how scalable it needs to be)

    You can get the Place averages using aggregate models:


    Aggregate model on race results where there’s an average on place, grouped by Event




Aggregate model on race results where there's an average on place, grouped by Location
Aggregate model where there's an average on place, grouped by Special
Summing those aggregations based on specific criteria may be trickier - do you want to report on all possible combinations of event location and class or just a few?

Just a few: Create an aggregate model for each possible combination, e.g.


Aggregate model on race results where there’s an average on place, grouped by Event with condition “Event = Village, Location=Upper, Class=Special”




Then have a table on this model with a SUM column summary on the Average field...

I know this only gets you the first part of what you want with Ui only formula fields you may be able to SUM of model lookups…


Eventually you may have to get into Javascript here.


Skuid documentation:


Aggregate Models :


UI Only Formula Fields:


Hi Anna,


Thanks for taking a look at this. I should have clarified that the values for “event”, “location” and “class” could be any number of 100+ different values depending upon which records are pulled into my dashboard, so setting specific conditions for each possible combination would not work for me (so it would be all possible combos, to answer your question).


I did figure out the first part (getting the place averages via an aggregate model), but summing them based on specific criteria does not seem doable.


Thanks,

Sarah

Hey @Smdrich



  • Are you using Salesforce? You might conceivably be able to this with SF Formula fields as this would give you the most flexibility for reusing these fields.




Are you building in api v1 or v2? Which Skuid version are you using?

I did check with our internal team and they mentioned that if you’re in V1, you can probably just use the math formulas for agg in Skuid labs (see the link below - these formulas basically surface skuid.aggregations.aggregate() in formula form). You’d just need to create your own key for finding matches if you want to match across multiple fields since the IF statement only checks a single field


https://github.com/skuid/skuid-labs/tree/master/experiments/formulaFunctions

Hi @“Anna Wiersema” - I am on V2 and yes, Salesforce.

Hey @Smdrich ! Just wanted to follow up. Were you able to use the math formulas in your version?

Hi @Germany3 - I was not as I am on V2 and those are for V1 it seems. My solution is to have the end users manually select the rows that they want to sum (I added a column summary on the aggregate value). Not ideal but seems to be the best option at the moment. We also have too many potential combos to create a key as suggested.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings