Glad it worked out! If you find more complicated use cases, it could be worth using the sumif formulas we have on skuid-labs. Here’s an example page of a modified version of that formula that would work for the use case where you need to show months with null sums.
{{index}}
IF({{index}}==1,{{Amount}},MODEL_LOOKUP(“Oppty”,“cml”,“index”,{{index}}-1) + {{Amount}})
{{index}}
IF({{index}}==1,{{sumAmount}},MODEL_LOOKUP(“OpptyAgg”,“cml”,“index”,{{index}}-1) + IF({{sumAmount}},{{sumAmount}},0))
IF({{index}}==1,0,MODEL_LOOKUP(“OpptyAgg”,“newcml”,“index”,{{index}}-1))
{{priorcml}} + IF({{sumAmount}},{{sumAmount}},0)
AGG__SUMIF(“OpptyAgg”,“sumAmount”,“index”,{{index}},“<=”)
Month
Year
var params = arguments[0],
$ = skuid.$;
console.log(params);
// This was written for Skuid 11.X, and has not been tested in other version
// Only tested in Chrome on Win & Mac
// Uses 2 unsupported Skuid APIs (skuid.aggregations and skuid.utils.getObjectProperty)
// general function that does the “if” in mathif to filter model to records where iffield=ifvalue
function mathAggIf(mathtype, modelname, fieldname, iffield, ifvalue, ifop, blanks) {
var model = skuid.$M(modelname);
if (!model) throw “Invalid Model provided in formula function”;
var modelRows = model.getRows();
function filterByField(item) {
switch(ifop) {
case “<”:
return skuid.utils.getObjectProperty(item, iffield) < ifvalue;
case “<=”:
return skuid.utils.getObjectProperty(item, iffield) <= ifvalue;
case “>”:
return skuid.utils.getObjectProperty(item, iffield) > ifvalue;
case “>=”:
return skuid.utils.getObjectProperty(item, iffield) >= ifvalue;
case “!=”:
return skuid.utils.getObjectProperty(item, iffield) != ifvalue;
default:
return skuid.utils.getObjectProperty(item, iffield) === ifvalue;
}
}
// skuid function that takes in these params, passes to filter function, and spits out the result
return skuid.aggregations.aggregate(
mathtype,
model,
fieldname,
{
countBlanks: blanks,
rows: modelRows.filter(filterByField)
}
);
}
// SUM & SUMIF
skuid.formula.Formula(
“SUM”,
// AGG__SUM(modelToAgg,fieldToAgg,countblanks) — what to put in formula field, copy & paste this
// since we can’t have these functions show up as choices in the Functions dropdown.
function(modelname, fieldname, blanks) {
return skuid.aggregations.aggregate(“sum”, skuid.$M(modelname), fieldname, {
countBlanks: blanks
});
},
{
namespace: “AGG”,
numArgs: 3,
returnType: “number”
}
);
skuid.formula.Formula(
“SUMIF”,
// AGG__SUMIF(modelToAgg,fieldToAgg,conditionField,conditionValue,conditionOperator,countblanks)
function(modelname, fieldname, iffield, ifvalue, ifop, blanks) {
return mathAggIf(“sum”, modelname, fieldname, iffield, ifvalue, ifop, blanks);
},
{
namespace: “AGG”,
numArgs: 6,
returnType: “number”
}
);
// AVG & AVGIF
skuid.formula.Formula(
“AVG”,
// AGG__AVG(modelToAgg,fieldToAgg,countblanks)
function(modelname, fieldname, blanks) {
return skuid.aggregations.aggregate(“avg”, skuid.$M(modelname), fieldname, { countBlanks: blanks });
},
{
namespace: “AGG”,
numArgs: 3,
returnType: “number”
}
);
skuid.formula.Formula(
“AVGIF”,
// AGG__AVGIF(modelToAgg,fieldToAgg,conditionField,conditionValue,conditionOperator,countblanks)
function(modelname, fieldname, iffield, ifvalue, ifop, blanks) {
return mathAggIf(“avg”, modelname, fieldname, iffield, ifvalue, ifop, blanks);
},
{
namespace: “AGG”,
numArgs: 6,
returnType: “number”
}
);
// MIN & MINIF
skuid.formula.Formula(
“MIN”,
// AGG__MIN(modelToAgg,fieldToAgg,countblanks)
function(modelname, fieldname, blanks) {
return skuid.aggregations.aggregate(“min”, skuid.$M(modelname), fieldname, {countBlanks: blanks});
},
{
namespace: “AGG”,
numArgs: 3,
returnType: “number”
}
);
skuid.formula.Formula(
“MINIF”,
// AGG__MINIF(modelToAgg,fieldToAgg,conditionField,conditionValue,conditionOperator,countblanks)
function(modelname, fieldname, iffield, ifvalue, ifop, blanks) {
return mathAggIf(“min”, modelname, fieldname, iffield, ifvalue, ifop, blanks);
},
{
namespace: “AGG”,
numArgs: 6,
returnType: “number”
}
);
// MAX & MAXIF
skuid.formula.Formula(
“MAX”,
// AGG__MAX(modelToAgg,fieldToAgg,countblanks)
function(modelname, fieldname, blanks) {
return skuid.aggregations.aggregate(“max”,skuid.$M(modelname), fieldname, {
countBlanks: blanks
});
},<br alt=“” name=“” rel=“” target=“” title=“”