Introduction:
Calculated fields was first introduced in Dynamics CRM 2015 and it further improved upon the ability to provide codeless solutions to power users to configure. You can learn more about its features from our earlier blog here.
It provided support for most CRM data types however the calculations and formulae supported for Date/Time fields were still limited.
With the latest Online update, they have enhanced the operations supported on Date/Time fields.
- DIFFINDAYS(start date or time, End date or time)
- DIFFINHOURS(start date or time, End date or time)
- DIFFINMINUTES(start date or time, End date or time)
- DIFFINWEEKS(start date or time, End date or time)
- DIFFINYEARS(start date or time, End date or time)
- DIFFINMONTHS(start date or time, End date or time)
The Rollup field has also been enhanced to support AVG as an aggregate calculation for Date/Time fields.
How does this help?
A very real scenario where this code be used is to calculate the Avg. time it takes for resolving a complaint.
The Actual Units fields in the Case entity calculates the total time spent on the case by adding up the times on the individual activities associated with the case. But what if you wanted to know just how long a case had been open – case createdon – case modifiedon.
Next it would be good to have the Avg. case resolution time per customer, to monitor if there are particular customers for which is usually takes too long to close a case. What we are looking out for here is Avg. Case closure time.
Walkthrough
Let us see how to go about getting this set up in no time.
- Add the Calculated field.
Since we have created a Duration field, we would calculate the difference between CreatedOn and ModifiedOn in minutes. Note. Duration field always expects the values to be provided in minutes.
- Create the Rollup field.
We will add the rollup field on Account to get the Avg. Resolution time.
Rollup the time for only resolved cases.
- Place both the fields on the Case and Account forms respectively.
- Now when you resolve the case, you will find that the case resolution time is auto calculated.
- We quickly closed a few cases for this account so that the avg. could be calculated
6. Now lets check how it looks on the Account form
Conclusion:
Though this takes a step further in supporting date/time data type in Calculated and Rollup fields, Rollup fields still do not support Date conditional operators like Last X months/years etc. So if I wanted to get the avg. case closure time in the last 6 months, I still cannot do that. Hoping to see this included in future version to support a full code-less power-user configuration solution.
Its just not this. Have a look at our mapping solution for Dynamics CRM, Maplytics which works from with CRM. Email us on crm@inogic.com for a trial or if you would like to see a live demo.