Prep Work:
Create 2 apps in CRM Analytics -- one to contain to contain new / modified work for UAT testing (PreProd Testing) and another to contain archived copies (Archived Dashboards). Additional apps may be created to contain the same assets as it moves though varying stages of approval. (example: PreProd > UAT Testing > Archive)
Production Deployment:
Example: User Story 1234 requests a color change and 2 additional filters for the 'Top Accounts' (Dashboard ID 1) contained in the Sales Analytics app.
'Top Accounts_post_1234' (ID 3) > Archived Dashboards 'Top Accounts' (ID 2) > Sales Analytics.
0 Comments
q_b = load "0Fb1T000000ob0mSAA/0Fc1T000006fBb4SAE";
q_a = load "0Fb1T000000bmx9SAA/0Fc1T000006fHN3SAM"; q_a=filter q_a by !('StageHGI' in ["Dormant", "Duplicate"]); q_b=filter q_b by 'OpportunityRecordType.Name' in ["Renewal Business - FRR","Cross-sell - FRR", "New Business - FRR", "Upsell/Add-On - FRR"]; q_b= filter q_b by date('Snapshot_Date_Year', 'Snapshot_Date_Month', 'Snapshot_Date_Day') in ["15 days ago".."15 days ago"]; q_a=filter q_a by 'RecordType.Name' in ["Renewal Business - FRR","Cross-sell - FRR", "New Business - FRR", "Upsell/Add-On - FRR"]; --q_a = foreach q_a generate 'Id' as 'Id','StageHGI' as 'StageHGI', case when ( 'StageHGI' == "Closed Won") then "Closed Won" when ('StageHGI' == "Closed Lost") then "Closed Lost" when('Deal_Status_MASTER__c' != "Forecast") then "Non Forecast" else "ok" end as 'Bins','TotalAACV' as 'TotalAACV', 'CloseDate' as 'CloseDate', case when date( 'CloseDate_Year','CloseDate_Month', 'CloseDate_Day') in ["current fiscal_quarter".."current fiscal_quarter"] then "ValidDt" else "InvalidDate" end as 'DateFilterA',case when 'RecordType.Name' == "Renewal Business - FRR" then 'TotalAACV' - 'AACV__c' else 'TotalAACV' end as 'RealAACV'; q_a = foreach q_a generate 'Id' as 'Id','StageHGI' as 'StageHGI', case when ('StageHGI' == "Closed Won") then "Closed Won" when ('StageHGI' == "Closed Lost") then "Closed Lost" when ('Deal_Status_MASTER__c' != "Forecast") then "Non Forecast" else "ok" end as 'Bins','TotalAACV' as 'RealAACV', 'CloseDate' as 'CloseDate', case when date( 'CloseDate_Year','CloseDate_Month', 'CloseDate_Day') in ["current fiscal_quarter".."current fiscal_quarter"] then "ValidDt" else "InvalidDate" end as 'DateFilterA'; res_a= foreach q_a generate 'Id' as 'Id','StageHGI' as 'StageHGI', 'Bins' as 'Bins','RealAACV' as 'RealAACV', 'CloseDate' as 'CloseDate', 'DateFilterA' as 'DateFilterA'; --q_b = foreach q_b generate 'Id' as 'Id','Snapshot_Date' as 'snapB','Bins' as 'Bins','TotalAACV' as 'TotalAACV', 'CloseDate' as 'CloseDate', case when date( 'CloseDate_Year','CloseDate_Month', 'CloseDate_Day') in ["current fiscal_quarter".."current fiscal_quarter"] then "ValidDt" else "InvalidDate" end as 'DateFilterB',case when 'OpportunityRecordType.Name' == "Renewal Business - FRR" then 'TotalAACV' - 'AACV__c' else 'TotalAACV' end as 'RealAACV'; q_b = foreach q_b generate 'Id' as 'Id','Snapshot_Date' as 'snapB','Bins' as 'Bins','TotalAACV' as 'RealAACV', 'CloseDate' as 'CloseDate', case when date( 'CloseDate_Year','CloseDate_Month', 'CloseDate_Day') in ["current fiscal_quarter".."current fiscal_quarter"] then "ValidDt" else "InvalidDate" end as 'DateFilterB'; res_b= foreach q_b generate 'Id' as 'Id','snapB' as 'snapB','Bins' as 'Bins','RealAACV' as 'RealAACV', 'CloseDate' as 'CloseDate', 'DateFilterB' as 'DateFilterB'; combo = group res_a by 'Id' full, res_b by 'Id'; combo= foreach combo generate res_a.'Id' as 'Id',first(res_a.'StageHGI') as 'StageHGI', sum(res_a.'RealAACV') as 'AmtA', first(res_a.'CloseDate') as 'CloseDateA', first(res_a.'DateFilterA' )as 'DateFilterA',first(res_a.'Bins') as 'BinA', first(res_b.'Bins') as 'BinB',first(res_b.'DateFilterB' )as 'DateFilterB', sum(res_b.'RealAACV') as 'AmtB', first(res_b.'CloseDate') as 'CloseDateB'; combo2= foreach combo generate 'Id' as 'Id', 'AmtA' as 'AmtA', 'CloseDateA' as 'CloseDateA', 'DateFilterA' as 'DateFilterA', 'BinA' as 'BinA', 'BinB' as 'BinB', 'DateFilterB' as 'DateFilterB', 'AmtB' as 'AmtB','CloseDateB' as 'CloseDateB', coalesce((case when 'BinB' !="ok" and 'DateFilterA'=="ValidDt" and 'BinA' =="ok" then 'AmtA' when 'BinB' =="ok" and DateFilterB =="InvalidDate" and 'DateFilterA' == "ValidDt" and 'BinA' == "ok" then 'AmtA' when ('BinB' is null and 'DateFilterA'=="ValidDt" and 'BinA' =="ok") then 'AmtA' when ('BinB' == "ok" and 'DateFilterB'=="ValidDt" and 'BinA' =="ok" and 'DateFilterA' =="ValidDt" and 'AmtA' !='AmtB') then ('AmtA' -'AmtB') when ('BinB' == "ok" and 'DateFilterB'=="ValidDt" and 'BinA' == "Closed Won") then 'AmtB' * (-1) when ('BinB' == "ok" and 'DateFilterB'=="ValidDt" and 'BinA' =="Closed Lost") then 'AmtB' * (-1) when ('BinB' == "ok" and 'DateFilterB' == "ValidDt" and 'DateFilterA'== "InvalidDate") then 'AmtB' * (-1) when ('BinB' == "ok" and 'DateFilterB' == "ValidDt" and 'DateFilterA' != "InvalidDate" and 'BinA' != "ok") then 'AmtB' * (-1)end),0) as 'Adjustments', (case when 'BinB' !="ok" and 'DateFilterA'=="ValidDt" and 'BinA' =="ok" then "Status Upgrade" when 'BinB' =="ok" and DateFilterB =="InvalidDate" and 'DateFilterA' == "ValidDt" and 'BinA' == "ok" then "PulledIn" when ('BinB' is null and 'DateFilterA' == "ValidDt" and 'BinA' =="ok") then "New" when 'BinB' =="ok" and 'DateFilterB' =="ValidDt" and 'BinA' =="ok" and 'DateFilterA' =="ValidDt" and 'AmtA' !='AmtB' then "Amt Inc or Dec" when ('BinB' =="ok"and 'DateFilterB'=="ValidDt" and 'BinA' == "Closed Won") then "Closed Won" when ('BinB' =="ok"and 'DateFilterB'=="ValidDt" and 'BinA' =="Closed Lost") then "Closed Lost" when ('BinB' == "ok" and 'DateFilterB' == "ValidDt" and 'DateFilterA' == "InvalidDate") then "Pushedout" when ('BinB' == "ok" and 'DateFilterB' == "ValidDt" and 'DateFilterA' != "InvalidDate" and 'BinA' != "ok") then "Downgraded" end) as 'AdjCode'; combo3 = group combo2 by 'AdjCode'; combo3 = foreach combo3 generate 'AdjCode' as 'AdjCode', sum('Adjustments') as 'Adjustments'; combo4 = filter combo3 by 'Adjustments' != 0; combo4 = group combo4 by 'AdjCode'; combo4 = foreach combo4 generate 'AdjCode' as 'AdjCode', sum('Adjustments') as 'Adjustments'; snap1 = filter combo by 'BinB' =="ok" and 'DateFilterB' =="ValidDt"and !('StageHGI' in["Dormant","Duplicate"]); snap1=group snap1 by all; snap1 = foreach snap1 generate "Starting Forecast" as 'AdjCode', sum('AmtB') as 'Adjustments'; snap2 = filter combo by 'BinA'=="ok" and 'DateFilterA' == "ValidDt"; snap2= group snap2 by all; snap2 = foreach snap2 generate "Ending Forecast" as 'AdjCode', sum('AmtA') as 'Adjustments'; finalRes = union snap1,combo4, snap2; Links is a CRMA widget class that is used to navigate between url's, pages, and layouts. The default of this widget is a button . However, one can also use images embedded in a container to 'house' links. In this use-case, we will be using 2 links-- Link # 1 to go from page 1 to page 2 . This link#1 is located in page 1. Link # 2 to go from page 2 back to page 1. This link#2 located in page 2. Step 1: drag container #1 in page 1, pick "deselect " file as its background image. Step 2: drag a link and maximize it inside container. Make it invisible by widget style>background color>custom and slide color all the way to left. Fill out the 'Link to' dialogue boxes to go from page 1 to page 2. Step 3: go to page 2, drag container#2,pick 'selected' file as its background image. Step 4: drag a link and maximize it inside container #2 Make it invisible by widget style>background color>custom and slide color all the way to left. Fill out the 'Link to' dialogue boxes to go from page 2 to page 1. Step 5: drop a text field next to the 2 containers to label buttons appropriately. "Show Page 2" and "Show page1" Snip below shows 'deselect' and 'select' images . "filterpanel_3": {
"parameters": { "filterItemOptions": { "propertyColor": "#091A3E", "valueColor": "#16325C" }, "filters": [ { "dataset": { "name": "zds_OpptyLineItemNew" }, "field": "insideOutsideUserField" } ], "itemsPerRow": 1, "showAllFilters": false, "title": { "separatorColor": "#E6ECF2", "text": { "align": "left", "color": "#091A3E", "fontSize": 16, "label": "Inside or Field Sales" }, "visible": true } }, "type": "filterpanel" }, "listselector_12": { "parameters": { "compact": false, "displayMode": "combo", Using the GET method in workbench enables user to retrieve the dependencies of CRMA assets. Snip below shows a retrieval of connected dependencies of a dataset. Here you see that the dataset with id of 0Fb1T0000oE....... has the following dashboards, recipes and app.
Funnel Chart with detail page faceting.(Bonus!: reordering stagenames w/o showing ordering field)3/5/2023 Below is SAQL code to create a funnel chart based on Stagename and also facet the segment into a table widget.The four different filters creates a separate datastrem to pick out specific rows (q,q2,q3,q4). The 'result=order result by OrdStage orders the rows and then generates the regular stage name.
q = load "x_Appl2Participation"; q2=filter q by 'Appl2Participation.Name' is not null; q3 = filter q by 'Appl2Participation.Status__c' == "Referred" ; q4 = filter q by 'Appl2Participation.contact2Appl.Actual_Start_Date__c' is not null; q = group q by 'Contact_Name__c'; q=foreach q generate 'Contact_Name__c',unique('Contact_Name__c') as 'Totals'; q1=group q by all; q1=foreach q1 generate "Attended" as 'Stage',count() as 'Totals'; q2 = group q2 by 'Contact_Name__c'; q2=foreach q2 generate 'Contact_Name__c' as 'Contact_Name__c',unique('Contact_Name__c') as 'Totals'; q2=group q2 by all; q2=foreach q2 generate "Applied" as 'Stage', count() as 'Totals'; q3=group q3 by all; q3=foreach q3 generate "Referred" as 'Stage', count() as 'Totals'; q4=group q4 by 'Contact_Name__c'; q4=foreach q4 generate unique('Contact_Name__c') as 'Totals'; q4=group q4 by all; q4=foreach q4 generate "Hired" as 'Stage',count() as 'Totals'; result=union q1,q2,q3,q4; result=group result by 'Stage'; result=foreach result generate 'Stage' as 'Stage', (case when 'Stage'=="Hired" then "4-Hired" when 'Stage'=="Attended" then "1-Attended" when 'Stage'=="Applied" then "2-Applied" when 'Stage'=="Referred" then "3-Referred" end) as 'OrdStage',sum('Totals') as 'Totals'; result=order result by 'OrdStage'asc; result=foreach result generate 'Stage' as 'Stage',sum('Totals') as 'Totals'; CODE BELOW IS FOR THE TABLE WIDGET WITH FUNNEL_2 beign the name of the above funnel chart. q = load "x_Appl2Participation"; q2=filter q by 'Appl2Participation.Name' is not null; q3 = filter q by 'Appl2Participation.Status__c' == "Referred" ; q4 = filter q by 'Appl2Participation.contact2Appl.Actual_Start_Date__c' is not null; q = group q by 'Contact_Name__c'; q=foreach q generate "Attended" as 'Stage', 'Contact_Name__c'; q2 = group q2 by 'Contact_Name__c'; q2=foreach q2 generate "Applied" as 'Stage','Contact_Name__c' as 'Contact_Name__c'; q3=foreach q3 generate "Referred" as 'Stage','Contact_Name__c' as 'Contact_Name__c'; q4=group q4 by 'Contact_Name__c'; q4=foreach q4 generate "Hired" as 'Stage','Contact_Name__c' as 'Contact_Name__c'; result=union q,q2,q3,q4; result2=filter result by 'Stage' =="{{column(funnel_2.selection, ["Stage"]).asObject()}}"; result2=foreach result2 generate 'Stage' as 'Stage', 'Contact_Name__c' as 'Contact_Name__c'; Most implementations in Einstein Analytics/CRMa start off with data profiling and clean-up. A typical use-care might be to audit an instance, figure out the list of CRMA assets and devide which ones to delete, preserve or modify. There is a tool called workbench which gives a CRMA dev/admin a quicker way to list these assets. Here are sample get/post statments.
Step 1: Start workbench https://workbench.developerforce.com/login.php Step 2: login using current salesforce instance credentials. Step 3: Get to the main workbench page and issue get/post,etc. Here are some examples Code below is for calculating 'prior period' and 'prior year'..somewhat tricky beause it is based on a primary data filter where user picks a varying range of date values--eg. current year, or 4 months to 3 months ago, or 2 months ago to today,etc.. Code for prior year just picks the starting date and ending date and moves it 1 year back.. plain and simple. The prior period calculates time elapsed (ex 4 to 5 months ago is 30 days) . It takes the startign DAte, offsets it by one day, then calculates back for 30 days (using above example)
q = load \"x_LS_Invoice_Volume\";\nq= filter q by {{row(Date_2.selection,[0],[\"min\",\"max\"]).asDateRange(\"date('Date_Year', 'Date_Month', 'Date_Day')\")}};q1 = foreach q generate 'LOB2' as 'lob2','Account.Type' as 'ty','Date' as 'invdt','Date_sec_epoch' as 'sec',toDate('Date_sec_epoch'- 31470526 ) as 'YrAgo', 'Date_sec_epoch'- 86400 as 'yesterday';\nq2=foreach q1 generate'invdt' as 'invDt','sec' as 'sec', 'YrAgo' as 'YrAgo','yesterday' as 'Yesterday';\nq3=group q2 by all;\nq3=foreach q3 generate min('sec') as 'StartingDtSec',max('sec') as 'EndingDtSec',toDate(min('sec')) as 'StartingDt',toDate(max('sec')) as 'EndingDt', min('YrAgo') as'StartingPrevYrDt',max('YrAgo') as 'EndingPrevYrDt',min('Yesterday') as 'PeriodEnd_seconds',toDate(min('Yesterday')) as 'PeriodEndDate';\nq4 = foreach q3 generate 'StartingDt' as 'StartingDt','StartingDtSec'as 'StartingDtSec','EndingDtSec' as 'EndingDtSec', 'EndingDt' as 'EndingDt', 'StartingPrevYrDt' as 'StartingPrevYrDt', 'EndingPrevYrDt' as 'EndingPrevYrDt','PeriodEnd_seconds' as 'PeriodEnd_seconds', 'PeriodEndDate' as 'PeriodEndDate',date_diff(\"day\",toDate('StartingDtSec'),toDate('EndingDtSec')) as 'delta';\nq5 = foreach q4 generate 'StartingDt' as 'StartingDt', 'EndingDt' as 'EndingDt','StartingDtSec'as 'StartingDtSec','EndingDtSec' as 'EndingDtSec', 'StartingPrevYrDt' as 'StartingPrevYrDt', 'EndingPrevYrDt' as 'EndingPrevYrDt', date_to_epoch('StartingPrevYrDt')as 'StartingPrevYrDt_seconds',date_to_epoch('EndingPrevYrDt')+ 86400 as 'EndingPrevYrDt_seconds', 'PeriodEnd_seconds' as 'PeriodEnd_seconds', 'PeriodEndDate' as 'PeriodEndDate','delta' as 'delta',toDate(date_to_epoch('StartingDt') - 86400 * 'delta' )as 'PeriodStartDt',date_to_epoch('StartingDt') - 86400 * 'delta' as 'PeriodStartDt_seconds'; q1 = filter q by {{column(timeStampText_4.selection,["Snapshot1"]).asEquality('timeStampText')}};
q1 = filter q1 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in {{cell(static_2.selection,0,"Valu").asString()}}; q2 = filter q by {{column(timeStampText_5.selection,["Snapshot2"]).asEquality('timeStampText')}}; q2 = filter q2 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in {{cell(q_FY2_1.selection,0,"Valu").asString()}}; result = group q1 by '{{column(static_1.selection, ["Valu"]).asObject()}}' full, q2 by '{{column(static_1.selection, ["Valu"]).asObject()}}'; result = foreach result generate coalesce(q1.'{{column(static_1.selection, ["Valu"]).asObject()}}', q2.'{{column(static_1.selection, ["Valu"]).asObject()}}' ) as '{{column(static_1.selection, ["Valu"]).asObject()}}', round(sum(q1.'Amt'),0) as 'Pipeline1 Amt',round(sum(q2.'Amt'), 0) as 'Pipeline2 Amt', round((sum(q1.'Amt')- sum(q2.'Amt')), 0) as 'Difference'; result = order result by ('{{column(static_1.selection, ["Valu"]).asObject()}}'asc); q3 = filter q by {{column(timeStampText_4.selection,["Snapshot1"]).asEquality('timeStampText')}}; q3 = filter q3 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in {{cell(static_2.selection,0,"Valu").asString()}}; q4 = filter q by {{column(timeStampText_5.selection,["Snapshot2"]).asEquality('timeStampText')}}; q4 = filter q4 by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in {{cell(q_FY2_1.selection,0,"Valu").asString()}}; |
|