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 characters 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
• Queue Label = 40
• 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
• Picklist and Multi Picklist LOV length = 255

Reference: 

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