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
No comments:
Post a Comment