Aggregate/summary table that tracks sales trends

  • 1
  • Question
  • Updated 5 years ago
  • Answered
I would like to create a aggregate/summary table that shows sales trend between months..

Account | Prior Month Sales | Current Month Sales | % Change

This may not be possible...I wanted to check...thanks
Photo of rocketc

rocketc

  • 722 Points 500 badge 2x thumb

Posted 5 years ago

  • 1
Photo of Anna Wiersema

Anna Wiersema

  • 10,890 Points 10k badge 2x thumb
Hi rocketc, can you explain a little bit more what you're looking for?

How are you tracking Sales right now? Just on the Opportunity object? do you have prior month sales and current month sales as fields in that object right now?

This kind of determines how you would do this / how easy it would be.
Photo of Anna Wiersema

Anna Wiersema

  • 10,890 Points 10k badge 2x thumb
For example, here's a sample aggregation I made comparing Last Month's Won Opportunities by Account vs. the Current Month's. But to show the percentage change out of the box, it would seem like you need Prior Month Sales, Current Month Sales & % Change to be fields on the object. Does this clarify things at all for you?

Photo of rocketc

rocketc

  • 722 Points 500 badge 2x thumb
Anna,

Thank you for your help.

I have a custom object called Orders.....In this object I have several fields including: [Order Date] & [Total Sale].

The Account object has a Master-Detail relationship with the Order_c object.

I only have the [Total Sale] field.
Photo of Anna Wiersema

Anna Wiersema

  • 10,890 Points 10k badge 2x thumb
Okay, so "Total Sale" is the total amount for that order?
Photo of rocketc

rocketc

  • 722 Points 500 badge 2x thumb
Yes, that is correct. I understand your suggestion in having a fields called Last Month, Current Month and percent change....not sure how to do this on the Orders object....but.....I guess I could create rollup fields on the Account object...for Past month and Current Month and use a formula field to calculate the percent change.....I wanted to check and see if I could use Skuid to creatively accomplish this...I have many similar calculations I would like to create....and I wanted to limit the number of roll-up fields I create....thank you again and please let me know if you can think of any creative ways to accomplish using skuid.
Photo of Anna Wiersema

Anna Wiersema

  • 10,890 Points 10k badge 2x thumb
Thank you, I will let you know if I think of anything. also let us know if you have any creative ideas of your own. :)
Photo of Anna Wiersema

Anna Wiersema

  • 10,890 Points 10k badge 2x thumb
Thanks for the clarification. It does sound like what you're trying to do is not possible out of the box, since you are trying to not just aggregate/summarize data, but also summarize/manipulate those aggregations. It could work if you add some formula fields (or maybe some custom Javascript.... but my default is "everything is possible with Javascript." I'd try the formula fields first if I were you.) e.g. on Account it may be possible to create a roll up summary of Orders last month, one for the current month and then a formula calculating the percent difference.

---
On the Skuid end, I'm including the xml for the page I made if you want to go in and look at it. The way it works is it takes the Closed Won Opportunities (for you this could just be Orders) and then calculates the some of last month's orders grouped by the account lookup field. then, I actually have another model exactly the same but with a condition where date = this month that powers the second table. If you've installed Skuid, you can copy and paste this page into your own org (using the view/edit XML page at the bottom of the Skuid page builder.) I think you could do something similar with your Orders object - have conditions or filters on Date, and then summaries of Total Sale and group it by Account Name.

Also here is some more information about aggregations in Skuid.
Photo of Anna Wiersema

Anna Wiersema

  • 10,890 Points 10k badge 2x thumb
that page:


<skuidpage showsidebar="true" showheader="true" tabtooverride="Opportunity">
<models>
<model id="OpportunityData" limit="100" query="true" createrowifnonefound="false" sobject="Opportunity" orderby="" type="aggregate">
<fields>
<field id="Amount" name="sumAmount" function="SUM"/>
</fields>
<conditions>
<condition type="fieldvalue" value="LAST_MONTH" enclosevalueinquotes="false" field="CloseDate"/>
<condition type="fieldvalue" value="Closed Won" enclosevalueinquotes="true" field="StageName"/>
</conditions>
<groupby method="simple">
<field id="Account.Name" name="accountName"/>
</groupby>
</model>
<model id="thismonth" limit="100" query="true" createrowifnonefound="false" sobject="Opportunity" orderby="" type="aggregate">
<fields>
<field id="Amount" name="sumAmount" function="SUM"/>
</fields>
<conditions>
<condition type="fieldvalue" value="Closed Won" enclosevalueinquotes="true" field="StageName"/>
<condition type="fieldvalue" value="THIS_MONTH" enclosevalueinquotes="false" field="CloseDate"/>
</conditions>
<groupby method="simple">
<field id="Account.Name" name="accountName"/>
</groupby>
</model>
</models>
<components>
<panelset type="custom">
<panels>
<panel width="50%">
<components>
<pagetitle model="OpportunityData">
<maintitle>Last Month</maintitle>
<subtitle>Sales</subtitle>
<actions/>
</pagetitle>
<skootable showconditions="true" showsavecancel="false" searchmethod="server" searchbox="false" showexportbuttons="false" pagesize="10" createrecords="false" model="OpportunityData" mode="read">
<fields>
<field id="Account.Name" name="accountName"/>
<field id="Amount" name="sumAmount">
<summaries>
<summary>sum</summary>
<summary>avg</summary>
</summaries>
</field>
</fields>
<rowactions/>
<massactions usefirstitemasdefault="true"/>
<views>
<view type="standard"/>
</views>
</skootable>
</components>
</panel>
<panel width="50%">
<components>
<pagetitle model="OpportunityData">
<maintitle>Current Month</maintitle>
<subtitle>Sales</subtitle>
<actions/>
</pagetitle>
<skootable showconditions="true" showsavecancel="false" searchmethod="server" searchbox="false" showexportbuttons="false" pagesize="10" createrecords="false" model="thismonth" mode="read">
<fields>
<field id="Account.Name" name="accountName"/>
<field id="Amount" name="sumAmount">
<summaries>
<summary>sum</summary>
<summary>avg</summary>
</summaries>
</field>
</fields>
<rowactions/>
<massactions usefirstitemasdefault="true"/>
<views>
<view type="standard"/>
</views>
</skootable>
</components>
</panel>
</panels>
</panelset>
</components>
<resources>
<labels/>
<css/>
<javascript/>
</resources>
</skuidpage>

Photo of rocketc

rocketc

  • 722 Points 500 badge 2x thumb
Anna, would it be possible to create two Aggregate/Summary models---
-Sum Sales for Current Month
-Sum Sales for Prior Month
And then Create a third model that "grabs" the data from the above models -- calculates the % difference and inserts results into a custom template....Note: I have no idea what I'm talking about...it just sound good.
Photo of Anna Wiersema

Anna Wiersema

  • 10,890 Points 10k badge 2x thumb
Haha welcome to my life!

Yes - Sum Sales Current Month & Sum Sales Prior Month (that's basically my idea with the two models on my page). you could also have just one model summing all sales and a filter that you can select to show "just last month" or "just this month" etc.

No - third model doing calculations. we do have a template component in Skuid, but it just lets you include html and multiple fields / custom text... no calculations :( I am not sure if there is any way to do this in Javascript. [you can include inline javascript in the resources tab that will be included in your Skuid page]