Using PrevGroupVal formula in Salesforce Reports

Use the Report Summary Formula PrevGroupVal to compare metrics between peer groups. For instance use it to compare won amounts over successive periods such as month-on-month comparisons.

To do this create a Report grouped by Close Date. The dates must be grouped by the period you require such as by Month or Quarter. Or you could use Close Month, Fiscal Period, etc.

Order the Groups in ascending date order so that comparison formula makes sense.

Create a Report summary field using PREVGROUPVAL which shows the amount variance compared to the previous period as a percentage. PREVGROUPVAL uses these parameters:

PREVGROUPVAL(summary_field, grouping_level [, increment]) where the

Summary_Field is the name of the grouped row or column (like AMOUNT:SUM). Grouping_Level is the summary level (such as CLOSE_DATE)

The formula is:


Set the Display level to specific groups

Run the report which will now include the month-on-month variance as a percentage.

Add a Chart with a line chart to represent the variance to appreciate the full effect of the comparison.

Identifying sales pipeline leaks

When assessing lost opportunities it’s instructive to review where in the pipeline are opportunities being lost. Put another way we want to know at what sales stages do we experience pipeline leakages.

While all sales pipelines will naturally leak (after all you can’t win every opportunity) it’s important to analyse where and why opportunities are lost. Lost deals point to problems in the sales process depending on where they leak from. These problems could include no emotional connection, information overload, undefined needs, poor on boarding, etc.

Opportunities should also not linger in the pipeline for too long. After a certain period the chances of closing an opportunity decrease dramatically and should exit the pipeline anyway.

Using standard Salesforce reports we can develop a view of where in the sales process deal are lost. To do this we will start by creating an Opportunity History Report.

  • Switch to Report Edit mode and select the Filter section
  • Update the Opportunity Close Date filter to reflect the period under review.
  • Add a To Stage filter and set it to Closed Lost as we want to analyse lost opportunities
  • Switch to the Outline section and add the From Stage and To Stage fields (in that order) under Group Rows
  • Then add a chart to the Report and select the Funnel chart type
  • For readability select the Show Values and Show Percentage chart options
  • This will render a funnel chart showing the stages where opportunities were lost as well as the number of opportunities and the percentage of lost opportunities they represent.

There you have it! You can now drill down to the underlying opportunities to assess where in the pipeline the opportunities leak.

Issue with role hierarchy reporting in Lightning

If an Org has two nodes immediately below the prime node there is an issue with including all records owned by users in these two nodes.

Under the prime node CDV there is a CEO and a Co CEO node.

role hierarchy

When defining the scope of records in a report filter the prime node, CDZ, cannot be selected.

report role selection

A choice has to be made between one or the other CEO node.

report two nodes

This is not a problem in a list view since all opportunities can be selected in the record scope without having to choose a node other than the prime node as is the case for reports.

The workaround is to either use a list view or create the report in Classic where all opportunities can be selected. Note that if you edit the report in Lightning after it has been created in Classic it will assume the above behaviour and the report will be limited to the first CEO node.

Update (4th Nov 2018): This is a known issue and will be fixed in a Winter ’19 patch

Getting started with CRM

1. Pick 3 burning issues to fix / improve or new initiatives to generate value. What is important is having a clear picture of the full vision and selecting a technology stack which will support adding functionality as required.

2. Identify metrics to measure the benefits for e.g. increase conversion of website visitors by x percent, decrease admin time to process sales by x days, etc. Start with a baseline measure even if it is an estimate. Build dashboards and reports which can readily show the improvement in the metrics.

3. Identify one person or team to drive and deliver the implementation. Has the mandate to make decisions or can easily get to decision makers.

When it comes to managing the overall change:

4. Ensure that the leadership team is fully bought-in and understands the impact of the new system on the ways of working (the change will not only be a system change but will require changes to processes and how people work)

5. Make the case for change and ensure that it is commonly understood (the why, what, how, etc) across the organization. Most people would agree that emails and spreadsheets are not the most efficient way to work but changing well-established habits and getting someone to log onto a new system and do a task differently requires change effort. Must have a clear answer to the question ‘What’s in it for me?’

6. Don’t underestimate the need for training and communication. These are just as important as the technology and directly lead to better adoption of the system.  

Zaheer Ismail

Zoho Creator data import

Zoho Creator is a useful and versatile platform. One of the more common requirements, especially when creating a new app, is to import data. While it has a decent import tool there is a limitation worth noting.

Using Numbers on a Mac I was unable to select a Numbers date format which was compatible with the Zoho form date format. The format is dd-MMM-yyyy. This cannot be changed – or I couldn’t easily find where to change this. Numbers does not support this format. As a result I had to use Google sheets.

Salesforce report: Logged-in users vs total user count

When measuring adoption it’s useful to see the number of logged in users for a given period as a percentage of the total user base. Since Salesforce reports can only report on what data is visible (as is to be expected) it’s not possible to compare logged in users to total users. There is a way around this by using a joined report.

  1. Create a joined report using User as the primary object. This will create a Users block.
  2. Drag a field from the Users object over to an empty space in the preview area. This will create a second Users block. It’s a good idea to use a field which is required for reporting purposes since the report will be grouped at this level.
  3. Filter the second block on logged-in users for the desired time frame.
  4. Create a cross-block formula field using the Record Count summary fields from each block to divide the number of logged in users from block 2 by the total number of users from block 1. This field will typically be called Logged In Percentage or something similar. Change the formula to field type to percentage.

Zaheer Ismail

How AI aids productivity in CRM

One of the more painful administrative tasks faced by Sales reps and account managers is keeping contact lists up to date. Sure, many platforms offer mail and calendar integration but there is still some work to be done, i.e. the contact has to be loaded in the contact application, such as Outlook,  before it can be added as a Contact in the CRM platform.

Recently, Apple introduced a feature whereby, an incoming caller number is tagged with a potential contact name based on iOS matching the incoming number with a number in the email signature of an exchange with the contact.

Salesforce Einstein provides a similar benefit which is called Automated Contacts.

This is a simple but beneficial time-saving device which highlights the benefits of embedded AI technologies in a CRM platform

Zaheer Ismail