Report like calculations in skuid charts

  • 1
  • Question
  • Updated 2 years ago
  • Answered
Hi,

We have a requirement to replicate a salesforce report in skuid charts. this report basically calculates the opportunity amount and divides the result with number of records. Report grouping is on account id. i am able to get the sum of opportunity amount as well as row count based on account grouping. however, i am unable to perform the calculations. any pointers will be helpful.

thanks
Photo of Sunny Sharma

Sunny Sharma

  • 678 Points 500 badge 2x thumb

Posted 3 years ago

  • 1
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
You can add a ui-only formula field to your model to do the calculation.
Photo of Sunny Sharma

Sunny Sharma

  • 678 Points 500 badge 2x thumb
thanks Matt. I already have the formula field in place. however, chart doesn't retain the ui-only formula field in category field dropdown.
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
I'm not following. Can you post some pictures or video to show what you mean and what you want?
Photo of Sunny Sharma

Sunny Sharma

  • 678 Points 500 badge 2x thumb
here is the model and chart code. 
<model id="NewBusinessHitRatio" limit="15" query="true" createrowifnonefound="false" adapter="" type="aggregate" sobject="Opportunity">         <fields>
            <field id="DBG_BoundCalc__c" name="sumDBGBoundCalcc" function="SUM"/>
            <field id="AIGCI_BookedCalc__c" name="sumAIGCIBookedCalcc" function="SUM"/>
            <field id="Id" name="countId" function="COUNT"/>
            <field id="CalcPercentage" uionly="true" displaytype="PERCENT" label="({{{sumDBGBoundCalcc}}}+{{{sumAIGCIBookedCalcc}}})/{{{countId}}}" precision="10" scale="2"/>
         </fields>
         <conditions>
            <condition type="fieldvalue" value="THIS_QUARTER" enclosevalueinquotes="false" field="DBG_FinalStatusDt__c" operator="lte"/>
            <condition type="multiple" value="" field="DBG_FinalStatusDt__c" operator="in" enclosevalueinquotes="false">
               <values>
                  <value>LAST_N_QUARTERS:4</value>
                  <value>THIS_QUARTER</value>
               </values>
            </condition>
            <condition type="fieldvalue" value="Product" enclosevalueinquotes="true" field="RecordType.DeveloperName"/>
            <condition type="fieldvalue" value="N" enclosevalueinquotes="true" field="DBG_BusinessTypeCd__c"/>
            <condition type="multiple" value="" field="StageName" operator="not in" enclosevalueinquotes="true">
               <values>
                  <value>Void</value>
                  <value>Suspended</value>
               </values>
            </condition>
            <condition type="modelmerge" value="" field="DBG_Broker__c" operator="=" model="BrokerModel" enclosevalueinquotes="true" mergefield="Id" novaluebehavior="deactivate"/>
         </conditions>
         <actions/>
         <groupby method="simple">
            <field id="DBG_FinalStatusDt__c" name="cyDBGFinalStatusDtc" function="CALENDAR_YEAR"/>
            <field id="DBG_FinalStatusDt__c" name="cqDBGFinalStatusDtc" function="CALENDAR_QUARTER"/>
         </groupby>
      </model>

<skuidvis__chart model="NewBusinessHitRatio" maintitle="New Business Hit Ratio" type="line" uniqueid="sk-r7kSK-317" subtitle="Rolling Four Qtrs" rendersnippet="" height="300px">                     <dataaxes>
                        <axis id="axis1" minvalue="0"/>
                     </dataaxes>
                     <categoryaxes>
                        <axis id="categories" categorytype="field" field="cqDBGFinalStatusDtc" template=""/>
                     </categoryaxes>
                     <serieslist>
                        <series valuefield="CalcPercentage" splittype="field" modelId="NewBusinessHitRatio" categoryField="cqDBGFinalStatusDtc" splitfield="cyDBGFinalStatusDtc"/>
                     </serieslist>
                     <colors/>
                     <legend layout="horizontal" halign="center" valign="bottom"/>
                     <allowedtypes>
                        <type>column</type>
                        <type>bar</type>
                     </allowedtypes>
                     <renderconditions logictype="and">
                        <rendercondition fieldmodel="BrokerModel" sourcetype="modelproperty" nosourcerowbehavior="deactivate" sourceproperty="hasRows"/>
                     </renderconditions>
                  </skuidvis__chart>
Photo of Sunny Sharma

Sunny Sharma

  • 678 Points 500 badge 2x thumb
some differences in aggregation due to change in requirement from report. 
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,224 Points 10k badge 2x thumb
Consider Salesforce roll up fields to aggregate the values of opportunities and count the number of records, then a Salesforce Calc field to do the math. Then bring the three fields into your Skuid model and chart them.
Photo of Sunny Sharma

Sunny Sharma

  • 678 Points 500 badge 2x thumb
Thanks Raymond. I deleted my previous comment as i just checked that rollup summary fields won't support dynamic date filters. so back to square one.
Photo of Karen Waldschmitt

Karen Waldschmitt, Official Rep

  • 8,260 Points 5k badge 2x thumb
Sunny~

Is this what you are trying to do: a chart that shows, by account, the average $ value of their opportunities? 



If so, here is what you need to do: 
Create an aggregate model. Really, you only need the first aggregation, the other 2 are for the table on the bottom of my screenshot above that confirms the calculations.


Since I want it by account, I grouped it by account ID.


I decided to use a line graph but you can use whatever type suits your fancy. 


You can use whatever you feel best describes the axes of your chart. Here's what I used.


Drag in whatever data you would like. If you want to make it more descriptive, you could even have a second line graph on top of this that shows the number of opportunities per account. 


Hope this works for you! Post back if you have any questions about this.

Thanks!
Karen
Photo of Sunny Sharma

Sunny Sharma

  • 678 Points 500 badge 2x thumb
Thanks Karen for your response but unfortunately this is not something i am looking for. this is something i am looking for:
For opportunity, we have 2 fields. let's say field A and field B. Broker field is custom lookup relationship between Account and Opportunity.
For a broker, for each quarter, i want to show sum of Field A and field B divided by number of opportunities.
Data limitation is for last 4 quarter and current quarter. Since it is a lookup relationship, rollup summary won't work (moreover, filter logic won't work as well).
i am getting row count, sum of field A, sum of field B. what next? I tried some snippets from this forum, but they don't work. 
Any thoughts on this?
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Sunny,

It's still not clear to me what isn't working. The formula field that you have in your original model looks fine (although you probably want to multiply by 100):
<field id="CalcPercentage" uionly="true" displaytype="PERCENT" label="({{{sumDBGBoundCalcc}}}+{{{sumAIGCIBookedCalcc}}})/{{{countId}}}" precision="10" scale="2"/>

What do you mean by:
chart doesn't retain the ui-only formula field in category field dropdown.
?
Photo of Sunny Sharma

Sunny Sharma

  • 678 Points 500 badge 2x thumb

 

CalCPercentage: ({{{sumDBGBoundCalcc}}}+{{{sumAIGCIBookedCalcc}}})/{{{countId}}}

 

Using CalcPercentage in data field and saved successfully. However, when i refresh skuid edit page it automatically CalcPercentage is replaced by other aggregated field.