CRM Analytics: Build Cumulative Percentage Chart

Use case: show the percentage of runners who finish the 5 km run in less than 35 minutes, less than 40 minutes, less than 45 minutes, and 45 minutes or above.

Here are the steps

1. Create a Compare Table

2. Clone "Count of Rows" column

3. Edit cloned "Count of Rows"

4. Select "Running Total" from f(x), you can add a header too

5. Switch to Query mode to calculate the percentage

Here is the original query

1. q = load "run_result";
2. q = group q by 'Bucket';
3. q = foreach q generate q.'Bucket' as 'Bucket', count(q) as 'A';
4. q = group q by 'Bucket';
5. q = foreach q generate 'Bucket', first(A) as 'A', sum(sum(A)) over ([..0] partition by all order by ('Bucket')) as 'B';
6. q = order q by 'Bucket' asc;
7. q = limit q 2000;

Update row #5 to

q = foreach q generate 'Bucket', first(A) as 'A', sum(sum(A)) over ([..0] partition by all order by 'Bucket') as 'B', sum(sum(A)) over ([..0] partition by all order by 'Bucket')/sum(sum(A)) over([..] partition by 'all') as 'cumulative (%)';

6. Format "cumulative (%)" to Percent or Custom

7. Switch the chart mode and select line chart

8. Hide A and B from Y-Axis and done

With this chart, we can easily understand the percentage of runners who complete the run in less than 35 minutes, less than 40 minutes, less than 45 minutes, and 45 minutes or above.

Bucket field in CRM Analytic

There are a few options to bucket field in CRM Analytics; let us see each option:

Scenario: field name = Option, bucket value A, B, C as "Executive", D, E, F as "Premier", all others as "Standard".

1. SAQL in the dashboard

Edit the widget and hit query mode

q = load "SalesData1";
q = filter q by 'Country' == "Singapore";
q = filter q by 'Option' is not null;
q = foreach q generate (case when Option in ["A","B","C"] then "Executive" when Option in ["D","E","F"] then "Premier" else "Standard" end) as 'Bucket';
q1 = group q by 'Bucket';
q1 = foreach q1 generate 'Bucket', count(q1) as 'Count';

This will work; however, there will be issues:

• Unable to broadcast selection as facet from the chart with SAQL because the identifier is unknown. When selecting the grouping, other widgets will get an error "Can't display this widget. This widget can't be displayed because there is a problem with its source query: Undefined identifier: "SAQL Field As". Make sure the "SAQL Field As" identifier exists and is spelled correctly without spaces.". However, the widget will still be able to receive filters from other widgets. You can deactivate "Broadcast selections as facets" for that query, but it is not ideal.
• Performance factors, system recommended: No Groupings After Projections and No Case Statements in Projections

Another sample:

q = load "SalesData1";
q = filter q by 'Country' == "Singapore";
q = filter q by 'Option' is not null;
q = group q by ('StartTime', 'Owner.Region__c');
q = foreach q generate q.'StartTime' as 'StartTime', (case when 'Owner.Region__c' is null then "Unknown" else 'Owner.Region__c' end) as 'Region', count(q) as 'Count';
q = order q by ('StartTime' asc, 'Region' asc);
q = limit q 2000;

2. Edit field value

Issue:

• Loss of the original field value
• The fields will not be grouped, even if they have the same value

From the screenshot above, the three Executive originally were A, B, and C; they are not combined by the system.

3. Using Recipe or Dataflow to create new fields

This is the most ideal option, but you have to edit the recipe or data.

System 'Name' fields length and maximum length

Common and useful system 'Name' fields length and maximum length:

• Account Name = 255
• Opportunity Name = 120
• Contact First Name = 40
• Contact Last Name = 80
• Lead First Name = 40
• Lead Last Name = 80
• User First Name = 40
• User Last Name = 80
• Profile Name = 255
• Permission Set Name = 80 
• Campaign Name = 80
• Task Subject = 255
• Event Subject = 255
• Case Subject = 255
• User Role Name = 80
• File Title = 255
• Report Name = 40
• Report Folder Name = 40
• Report Type Name = 80
• Dashboard Name = 80
• Dashboard Folder Name = 40
• List View Name = 40
• Custom Object Name = 80
• Record Type Name = 80
• Record Type Description = 255
• Custom Field Label = 40
• Field Help Text = 510
• Field Description = 1000

Reference: 

• Maximum length for system 'Name' fields
• IdeaExchange: Increase Name field length from 80 to 255

Salesforce: Report Inline Editing Limitation and Issue

Report Inline Editing is a great feature where users are able to edit the data (multi fields and multi rows) easily via report. The same thing can be done via list view, but more flexible than list view:

• Not all users able to create/edit public list view
• Need to add record type as a filter in list view if the object has multiple record type
• List view inline editing does not support controlling or dependent picklists

So, the report is a perfect option to cover the above issues. Report inline editing also honors the user has the right permissions to edit the data, only when these conditions are met for Object Permissions, Field Level Security, and Page Layout.

• Users have edit access to the associated object.
• Users have edit access to the associated field.
• The associated field is included and editable in the page layout assigned to the user profile.

However, not all fields are supported by report inline editing, e.g. Opportunity Amount, for complete reference, check out this article Updating Multiple Fields Inline on the Report Run Page in Lightning Experience.

As always, nothing is perfect in this world and Salesforce's great community has put some ideas to make this feature better:

• Reports - Inline Editing all Text fields (long/rich) (840 points)
• Report Inline Editing - Permission Sets (730 points)
• Inline Editing on Joined Reports (150 points)
• Report inline edit be assigned in profiles (70 points)

Issues

There is a minor issue related to data validation, e.g. you add a validation rule to maintain a business rule, however, if the system is unable to edit the record because of that rule, the system does not show clearly what is the cause of the error.

Another issue related to this Report Inline Editing: Dependent Field Requirement Should Match Record Pages (90 points), in summary, if the dependent field is marked as mandatory in page layout, however, no values of the dependent field are assigned to a value in the controlling field, inline report editing still requires the user to select a value, although no value is available in the dropdown too.

Reference:

• Updating Multiple Fields Inline on the Report Run Page in Lightning Experience
• Considerations for Inline Editing in a List View in Lightning Experience

Salesforce field deletion

In a blog written many years back Delete Custom Field, we shared items that will block and allow for field deletion.

Undelete field in Salesforce is also very simple, it will restore the field with data, but the admin need to note and perform a few actions as mentioned here:

• How to restore deleted Salesforce fields 
• Manage Deleted Custom Fields 

Fields deleted with the data will be stayed in the "recycle bin" for 15 days before it will be hard deleted by Salesforce.

A few items to note when the fields are in the recycle bin:

• You can see and open field detail from the object "Fields & Relationships" menu then click the "Deleted Fields" button
• The field API name will be changed with an additional _del, such as Test_Date__c --> Test_Date_del__c
• You can no longer query the field
• The field metadata will no longer exist

To check who and when delete the custom field is deleted, check the "View Setup Audit Trail", the action should start with "Deleted custom field " 

Reference:

• Delete Custom Field
• How to restore deleted Salesforce fields 
• Manage Deleted Custom Fields 

Salesforce: Query on Group and GroupMember

Group object is quite unique as it stores multiple types of data, from Public Group, Queue, Role, etc., you can see all the Type values in this article Group.

Note: The Type for Public Group is Regular

Sample query: SELECT Type, COUNT(Id) FROM Group GROUP BY Type

For user members in Group and Queue can be a query to the GroupMember object. Sample query:

All queue with queue members
SELECT Id, GroupId, Group.Name, UserOrGroupId FROM GroupMember WHERE Group.Type = 'Queue'

All public group with public group members
SELECT Id, GroupId, Group.Name, UserOrGroupId FROM GroupMember WHERE Group.Type = 'Regular'

All inactive users in a queue or public group
SELECT Id, GroupId, Group.Name, Group.Type, UserOrGroupId FROM GroupMember WHERE UserOrGroupId IN (SELECT Id FROM User WHERE IsActive = false) ORDER BY Group.Name

Reference:

• GroupMember
• Group