PREVGROUPVAL function

  • 1
  • Question
  • Updated 3 years ago
  • Answered
I am trying to build out an aggregate model on a custom object for forecasting. In Salesforce reports you can create formula fields that reference previous group values to create formulas to show percent change in numbers week over week, month over month, etc. Is there some sort of ui-only field functionality in skuid that would allow me to reference a previous value in a table to report the % changes? Thanks!
Photo of Megan Minahan

Megan Minahan

  • 726 Points 500 badge 2x thumb

Posted 3 years ago

  • 1
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
Megan,

You should be able to use the Model Lookup function to get corresponding values from the other table and run your calculation as part of the same formula field.

Bill
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,056 Points 20k badge 2x thumb
Bill, great idea! It intrigued me so I took it and ran with it. The other key piece that will help here is that you can use Merge Syntax to get access to each Model's row index within the list of all rows, e.g. first row's index is 0, 2nd row's index is 1, 3rd row's index is 2, etc. So you need to create a Ui-Only number formula field, e.g. a field named "myIndex", to keep track of the row's index with formula {{{index}}}. Then you can do a Model Lookup like this where you look up the value of an aggregation / grouping field for the previous row, like this:

MODEL_LOOKUP("PagesByWeek","countId","myIndex",VALUE({{{index}}}-1))

For my example page, I did an Aggregate Model on the skuid Page object to see the weekly percent change in the number of Skuid Pages being created in a particular org.



Here's the XML for this page:

<skuidpage unsavedchangeswarning="yes" personalizationmode="server" showsidebar="true" showheader="true">   
   <models>
      <model id="PagesByWeek" limit="" query="true" createrowifnonefound="false" adapter="" type="aggregate" sobject="skuid__Page__c" orderby="CALENDAR_YEAR(CreatedDate) ASC NULLS FIRST, WEEK_IN_YEAR(CreatedDate) ASC NULLS FIRST">
         <fields>
            <field id="Id" name="countId" function="COUNT"/>
            <field id="prevGroupCountId" uionly="true" displaytype="FORMULA" label="Previous Group Count Id" precision="9" scale="0" readonly="true" returntype="DOUBLE">
               <formula>MODEL_LOOKUP("PagesByWeek","countId","myIndex",VALUE({{{index}}}-1))</formula>
            </field>
            <field id="PercentChange" uionly="true" displaytype="FORMULA" label="% Change from Previous Week" readonly="true" returntype="PERCENT" precision="9" scale="2">
               <formula>(({{{prevGroupCountId}}}-{{{countId}}})*100)/{{{countId}}}</formula>
            </field>
            <field id="myIndex" uionly="true" displaytype="FORMULA" label="Row Index" precision="9" scale="0" readonly="true" returntype="DOUBLE">
               <formula>{{{index}}}</formula>
            </field>
            <field id="YearWeek" uionly="true" displaytype="FORMULA" readonly="true" returntype="TEXT" label="Calendar Week">
               <formula>{{{yearCreated}}} + "_" + {{{weekCreated}}}</formula>
            </field>
         </fields>
         <conditions>
            <condition type="fieldvalue" field="skuid__Type__c" operator="=" inactive="true" enclosevalueinquotes="true" name="__autofilter__skuid__Type__c" state="filterableoff" value=""/>
         </conditions>
         <actions/>
         <groupby method="simple">
            <field id="CreatedDate" name="yearCreated" function="CALENDAR_YEAR"/>
            <field id="CreatedDate" name="weekCreated" function="WEEK_IN_YEAR"/>
         </groupby>
      </model>
   </models>
   <components>
      <skuidvis__chart model="PagesByWeek" maintitle="Pages created week over week" type="line" uniqueid="sk-3lsePn-86">
         <dataaxes>
            <axis id="axis1"/>
         </dataaxes>
         <categoryaxes>
            <axis id="categories" categorytype="template" field="countId" template="{{{yearCreated}}}_{{{weekCreated}}}"/>
         </categoryaxes>
         <serieslist>
            <series valuefield="countId" splittype="none" modelId="PagesByWeek"/>
         </serieslist>
         <colors/>
         <legend layout="horizontal" halign="center" valign="bottom"/>
         <allowedtypes>
            <type>line</type>
            <type>spline</type>
            <type>area</type>
            <type>areaspline</type>
         </allowedtypes>
      </skuidvis__chart>
      <skootable showconditions="true" showsavecancel="false" showerrorsinline="true" searchmethod="client" searchbox="true" showexportbuttons="false" pagesize="10" createrecords="false" model="PagesByWeek" buttonposition="" mode="readonly" uniqueid="sk-3lmgqf-101" emptysearchbehavior="query">
         <fields>
            <field id="myIndex" valuehalign="" type="" decimalplaces=""/>
            <field id="CreatedDate" name="yearCreated" valuehalign="" type="">
               <label>Calendar Year</label>
            </field>
            <field id="CreatedDate" name="weekCreated" valuehalign="" type="">
               <label>Week in Year</label>
            </field>
            <field id="Id" name="countId" valuehalign="" type="">
               <label># of Pages created this Week</label>
            </field>
            <field id="prevGroupCountId" decimalplaces="" valuehalign="" type="">
               <label># of Pages created previous week</label>
            </field>
            <field id="PercentChange" decimalplaces="" valuehalign="" type=""/>
         </fields>
         <rowactions/>
         <massactions usefirstitemasdefault="true"/>
         <views>
            <view type="standard"/>
         </views>
         <filters>
            <filter type="select" filteroffoptionlabel="Page Type" createfilteroffoption="true" affectcookies="true" autocompthreshold="25" conditionsource="auto" labelmode="auto" conditionfield="skuid__Type__c"/>
         </filters>
         <searchfields/>
      </skootable>
   </components>
   <resources>
      <labels/>
      <javascript/>
      <css/>
   </resources>
   <styles>
      <styleitem type="background" bgtype="none"/>
   </styles>
</skuidpage>
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,056 Points 20k badge 2x thumb
What version of Skuid are you running?
Photo of Megan Minahan

Megan Minahan

  • 726 Points 500 badge 2x thumb
version 7.7
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
Megan,

I am running 7.27.  I was able to install Zach's page and it is working.

I am not sure what release the Model Lookup function was added.  Can you update to the newest Skuid release?

Thanks,

Bill
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,056 Points 20k badge 2x thumb
Bill is right, the MODEL_LOOKUP() function was added in a later release, you should install the latest Skuid version, either Banzai Update 6 or Banzai Update 7. 
Photo of Megan Minahan

Megan Minahan

  • 726 Points 500 badge 2x thumb
Thank you so much Zach and Bill! The update helped! This is skuidtastic! :)
Photo of Bill McCullough

Bill McCullough, Champion

  • 12,436 Points 10k badge 2x thumb
Zach...I really like the use of the Index as a way to get to the previous value.  Thanks for running with this idea.  I think this example has a lot of uses!  Best!  Bill