Hi i’m trying to create a table and graph to show a running total (or cumulative total), but not finding a way to do that in skuid (pretty simple in excel)
So example:
Day 1 sales are 93$ so cumulative total for that day would be 93$
Day 2 sales are 251$ so cumulative total for that day would be 344$ (Day 1 + day 2)
Day 3 sales are 126$ so cumulative total for that day would be 470 (Day 1 + day 2+ day 3) and so on…
any ideas would be appreciated
Thx
+17Check out about 35 minutes into this Nautilus Notes video:
+7Here’s an example of that method (on SFDC, Millauu 11.2.24). I feel like I’ve run into issues with it on larger models in the past, but it’s been a few months and could have been error on my part.
<skuidpage unsavedchangeswarning="yes" personalizationmode="server" showsidebar="true" showheader="true">
<models>
<model id="Oppty" limit="50" query="true" createrowifnonefound="false" datasource="salesforce" sobject="Opportunity">
<fields>
<field id="RecordTypeId"/>
<field id="Id"/>
<field id="Name"/>
<field id="Amount"/>
<field id="index" uionly="true" displaytype="FORMULA" label="index" ogdisplaytype="TEXT" readonly="true" returntype="DOUBLE" precision="9" scale="0">
<formula>{{index}}</formula>
</field>
<field id="cml" uionly="true" displaytype="FORMULA" label="cml" ogdisplaytype="TEXT" precision="12" scale="2" readonly="true" returntype="CURRENCY">
<formula>IF({{index}}==1,{{Amount}},MODEL_LOOKUP("Oppty","cml","index",{{index}}-1) + {{Amount}})</formula>
</field>
</fields>
<conditions>
<condition type="fieldvalue" value="0.05" enclosevalueinquotes="false" field="Amount" operator="gt"/>
</conditions>
<actions/>
</model>
</models>
<components>
<skootable showconditions="true" showsavecancel="true" showerrorsinline="true" searchmethod="server" searchbox="true" showexportbuttons="false" hideheader="false" hidefooter="false" pagesize="10" alwaysresetpagination="false" createrecords="true" model="Oppty" buttonposition="" mode="read" allowcolumnreordering="true" responsive="true" uniqueid="sk-3XoU-1072">
<fields>
<field id="RecordTypeId" uniqueid="fi-3XoU-1073"/>
<field id="Id" uniqueid="fi-3XoU-1074"/>
<field id="Name" uniqueid="fi-3XoU-1075"/>
<field id="Amount" uniqueid="fi-3XoU-1076"/>
<field id="index" uniqueid="fi-3XoU-1077"/>
<field id="cml" uniqueid="fi-3XoU-1078"/>
</fields>
<rowactions>
<action type="edit"/>
<action type="delete"/>
</rowactions>
<massactions usefirstitemasdefault="true">
<action type="massupdate"/>
<action type="massdelete"/>
</massactions>
<views>
<view type="standard"/>
</views>
</skootable>
</components>
<resources>
<labels/>
<javascript/>
<css/>
<actionsequences uniqueid="sk-3XQa-316"/>
</resources>
<styles>
<styleitem type="background" bgtype="none"/>
</styles>
</skuidpage>
Hi guys,
Finally was able to update my skuid version to the latest and was hoping that would fix the issue I first experienced, but it seems it still does not work for me.
I used the example above and works on a Basic Skuid model, but when trying this with an aggregate model, it does not.
Any ideas why and what I can do to make it work? as there can be multiple transactions for the day that I need to group
it just returns the same amount from the row and does not add up the previous row
Thx
+7I think it works with Agg, but at least for me it needs a condition to remove the nulls since any time periods with a null Amount throw things off. Example -
<skuidpage unsavedchangeswarning="yes" personalizationmode="server" showsidebar="true" showheader="true">
<models>
<model id="Oppty" limit="50" query="true" createrowifnonefound="false" datasource="salesforce" sobject="Opportunity">
<fields>
<field id="RecordTypeId"/>
<field id="Id"/>
<field id="Name"/>
<field id="Amount"/>
<field id="index" uionly="true" displaytype="FORMULA" label="index" ogdisplaytype="TEXT" readonly="true" returntype="DOUBLE" precision="9" scale="0">
<formula>{{index}}</formula>
</field>
<field id="cml" uionly="true" displaytype="FORMULA" label="cml" ogdisplaytype="TEXT" precision="12" scale="2" readonly="true" returntype="CURRENCY">
<formula>IF({{index}}==1,{{Amount}},MODEL_LOOKUP("Oppty","cml","index",{{index}}-1) + {{Amount}})</formula>
</field>
</fields>
<conditions>
<condition type="fieldvalue" value="0.05" enclosevalueinquotes="false" field="Amount" operator="gt"/>
</conditions>
<actions/>
</model>
<model id="OpptyAgg" query="true" createrowifnonefound="false" datasource="salesforce" sobject="Opportunity" type="aggregate" orderby="CALENDAR_YEAR(CloseDate), CALENDAR_MONTH(CloseDate)">
<fields>
<field id="Amount" name="sumAmount" function="SUM"/>
<field id="index" uionly="true" displaytype="FORMULA" label="index" ogdisplaytype="TEXT" readonly="true" returntype="DOUBLE" precision="17" scale="0">
<formula>{{index}}</formula>
</field>
<field id="cml" uionly="true" displaytype="FORMULA" label="cml" ogdisplaytype="TEXT" readonly="true" returntype="CURRENCY" precision="17" scale="2">
<formula>IF({{index}}==1,{{sumAmount}},MODEL_LOOKUP("OpptyAgg","cml","index",{{index}}-1) + IF({{sumAmount}},{{sumAmount}},0))</formula>
</field>
<field id="priorcml" uionly="true" displaytype="FORMULA" label="prior cml for test" ogdisplaytype="TEXT" readonly="true" returntype="CURRENCY" precision="17" scale="2">
<formula>IF({{index}}==1,0,MODEL_LOOKUP("OpptyAgg","newcml","index",{{index}}-1))</formula>
</field>
<field id="newcml" uionly="true" displaytype="FORMULA" label="newcml for test" ogdisplaytype="TEXT" readonly="true" returntype="CURRENCY" precision="17" scale="2">
<formula>{{priorcml}} + IF({{sumAmount}},{{sumAmount}},0)</formula>
</field>
</fields>
<conditions>
<condition type="fieldvalue" value="0.0" enclosevalueinquotes="false" field="Amount" operator="gt"/>
</conditions>
<actions/>
<groupby method="simple">
<field id="CloseDate" name="cmCloseDate" function="CALENDAR_MONTH"/>
<field id="CloseDate" name="cyCloseDate" function="CALENDAR_YEAR"/>
</groupby>
</model>
</models>
<components>
<tabset rememberlastusertab="true" defertabrendering="true" uniqueid="sk-Wx-276">
<tabs>
<tab name="Basic" loadlazypanels="true">
<components>
<skootable showconditions="true" showsavecancel="true" showerrorsinline="true" searchmethod="server" searchbox="true" showexportbuttons="false" hideheader="false" hidefooter="false" pagesize="10" alwaysresetpagination="false" createrecords="true" model="Oppty" buttonposition="" mode="read" allowcolumnreordering="true" responsive="true" uniqueid="sk-3XoU-1072">
<fields>
<field id="RecordTypeId" uniqueid="fi-3XoU-1073"/>
<field id="Id" uniqueid="fi-3XoU-1074"/>
<field id="Name" uniqueid="fi-3XoU-1075"/>
<field id="Amount" uniqueid="fi-3XoU-1076"/>
<field id="index" uniqueid="fi-3XoU-1077"/>
<field id="cml" uniqueid="fi-3XoU-1078"/>
</fields>
<rowactions>
<action type="edit"/>
<action type="delete"/>
</rowactions>
<massactions usefirstitemasdefault="true">
<action type="massupdate"/>
<action type="massdelete"/>
</massactions>
<views>
<view type="standard"/>
</views>
</skootable>
</components>
</tab>
<tab name="Agg" loadlazypanels="true">
<components>
<skootable showconditions="true" showsavecancel="false" showerrorsinline="true" searchmethod="server" searchbox="true" showexportbuttons="false" hideheader="false" hidefooter="false" pagesize="10" alwaysresetpagination="false" createrecords="false" model="OpptyAgg" buttonposition="" mode="readonly" allowcolumnreordering="true" responsive="true" uniqueid="sk-Vy0-447" heading="Agg">
<fields>
<field id="index" hideable="true" uniqueid="fi-VyZ-866"/>
<field id="Amount" name="sumAmount" uniqueid="fi-Vy0-448"/>
<field id="CloseDate" name="cmCloseDate" uniqueid="fi-Vy0-449">
<label>Month</label>
</field>
<field id="CloseDate" name="cyCloseDate" uniqueid="fi-Vy0-450">
<label>Year</label>
</field>
<field id="cml" hideable="true" uniqueid="fi-W1B-330"/>
</fields>
<rowactions/>
<massactions usefirstitemasdefault="true"/>
<views>
<view type="standard"/>
</views>
</skootable>
</components>
</tab>
</tabs>
</tabset>
</components>
<resources>
<labels/>
<javascript>
<jsitem location="inlinesnippet" name="newSnippet" cachelocation="false">var params = arguments[0],
$ = skuid.$;
console.log(params);</jsitem>
</javascript>
<css/>
<actionsequences uniqueid="sk-3XQa-316"/>
</resources>
<styles>
<styleitem type="background" bgtype="none"/>
</styles>
</skuidpage>
Hey Matt,
the updated CML formula & condition added you have on this page, made it work for me now!
You are now my new favorite person in this world, and shall name my first boy after you 🙂
**Fine print: As long as my wife agrees 😉 ***
+7Glad 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=“”
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.