Need to perform rollup on fields within same entity (not supported) to calculate MTD sales for a user

(0) Share

Report

Posted on by Daniel at VNS

We have an entity called Sales Orders where we track details of every Sales Order placed, including Net Product Profit, Net Service Profit, Date of Sale, and the Sales Rep's name.

I need to create a process that in any given calendar month populates a field in Sales Order with the month-to-date (MTD) Net Profit for the selected Sales Rep (lookup in the form). For example, if Sales Rep A made these sales:

Date	Sales Rep	Net Product Profit	Net Service Profit	MTD Net Profit - Sales Rep A
1/1/16	A	$100	$0	$100
1/5/16	A	$50	$100	$250
1/7/16	A	$0	$10	$260
1/7/16	B	$25	$0	$260
2/1/16	A	$5	$25	$30
2/1/16	B	$10	$15	$30

Then field for Sales Rep A's MTD sales on 1/7/16 would contain the result $260, etc.

There would not be more than 2 reps, and their MTD net profit is tracked separately, from the input of net product profit and net service profit on each Sales Order.

I initially tried to do this with a Rollup until I realized that it cannot be run against fields in the same entity - only related entities.

I considered creating a separate entity just to track this, which the rollup could run against, but that seems like a very inefficient way to solve this problem and I think I'm probably missing an obvious solution, and need some fresh perspective.

Maybe an asynch workflow?

I would like to try and do this with the OOB tools that CRM 2016 has provided - Rollups, Workflow, Calculations, Business Rules, etc. My JS abilities are also limited so all the more reason to use OOB tools.

And of course, it has to be done in a supported method. As a user of CRM since v3.0, I've had to suffer through a few painful upgrades where I had to rip out a lot of unsupported code. Going forward I want to try and do things in a manner that will make future upgrades easier.

Thank you in advance for any input.

*This post is locked for comments

All responses (3)

Answers (1)

Verified answer

PS 23,577 on at

Like (1)

Report

RE: Need to perform rollup on fields within same entity (not supported) to calculate MTD sales for a user

Hi Daniel,

Creating a process or roll up field would not be a recommended solution for this; as you have to calculate it every month and then you will have to change query of process or rollup field every time.

I would recommend creating Goals for each Sales Rep. You can create 12 Goals per year or One Parent Goal with 12 child goals for each Sales Rep. Following are the steps:

Step 1: Create a new field on Sales order, 'Net Profit'; this will be a calculated field and sum of Net Product profit + Net Service Profit

Step 2: Go to Sales>>Goals>>Create a new goal then create a new goal metric:

Follow the screenshots below:

Assumption: Net Product/Service Profit fields are currency/money fields

Tip: While creating the goal, you can also enter any sales target for the month, in order to track the performance

Note: The actual usage of goal management can be different from what is suggested here, but this fits your current requirement. let me know, if you are not comfortable with this and we can together think of any new possibilities

Here's more about goals: http://crmbook.powerobjects.com/basics/analytics/goal-management/

https://msdn.microsoft.com/en-us/library/gg309258.aspx?f=255&MSPPError=-2147217396
Daniel at VNS on at

Like (0)

Report

RE: Need to perform rollup on fields within same entity (not supported) to calculate MTD sales for a user

This info is stored in the Sales Order entity fields. We do a lookup to the User only to document the name of who 'user A or B' is. Then we would track each of their profit, for service and product separately, for the calendar month of the sale in each Sales Order record.
PS 23,577 on at

Like (0)

Report

RE: Need to perform rollup on fields within same entity (not supported) to calculate MTD sales for a user

Where is net product profit and net service profit stored?? I mean on which entity, user??