|
q = load "x_pokeOppty"; q = foreach q generate q.'mv_prod.ProductCode' as 'mv_prod.ProductCode', q.'Gross_Margin_Dollars__c' as 'Gross_Margin_Dollars__c','Acct.CreatedDate' as 'Acct.CreatedDate',day_in_week(toDate('Acct.CreatedDate_sec_epoch')) as 'DayCreated','Acct.CreatedDate_Year' as 'CreatedDtYear'; q = foreach q generate 'mv_prod.ProductCode', 'Gross_Margin_Dollars__c','Acct.CreatedDate', 'DayCreated','CreatedDtYear',case when ('DayCreated'==1) then "Sunday"when ('DayCreated'==2) then "Monday" when ('DayCreated'==3) then "Tuesday" when ('DayCreated'==4) then "Wednesday" when ('DayCreated'==5) then "Thursday" when ('DayCreated'==6) then "Friday" when ('DayCreated'==7) then "Saturday" end as 'dayString'; q= group q by ('dayString','DayCreated','CreatedDtYear'); q=foreach q generate 'DayCreated' as 'DayCreated','dayString' as 'daystring', 'CreatedDtYear', count() as 'dayCount'; q=order q by 'DayCreated' ; 
0 Comments
Value Display sortpivots sort measures
1 User_Division__x Team | Owner | Region -- 2Initial Call Initial Call - -- 3Initial Call - Yes Schedule Meeting Initial Call - Yes Schedule Meeting - --  Here is a sample of a table widget with totals with bindings used to sort meaures or dimensions (since faceting is needed to facilitate interactivity of dashboards). Projections need their API names vs. string labeling. Snip is pseudocode(almost)
q = load "Activities"; q_A = filter q by 'Type_of_Call__c' == "Proposal Revision" && 'Proposal_Meeting_Result__c' in ["Maybe - Revise Proposal", "No - Create New Activity", "Yes - Deal is Closed Won!"]; q_B = filter q by 'Type_of_Call__c' == "Proposal Revision" && 'Proposal_Meeting_Result__c' == "Yes - Deal is Closed Won!"; q_C = filter q by 'Type_of_Call__c' == "Initial Call" && 'Result_of_Call__c' in ["CFA, Successful", "No, Successful", "No, Unsuccessful", "Yes, Schedule Meeting"];q_A = group q_A by rollup('{{cell(static_3.selection, 0, "Value").asObject()}}'); q_A = order q_A by ('{{cell(static_3.selection, 0, "Value").asObject()}}' asc nulls first); q_B = group q_B by rollup('{{cell(static_3.selection, 0, "Value").asObject()}}'); q_B = order q_B by ('{{cell(static_3.selection, 0, "Value").asObject()}}' asc nulls first); q_C = group q_C by rollup('{{cell(static_3.selection, 0, "Value").asObject()}}'); q_C = order q_C by ('{{cell(static_3.selection, 0, "Value").asObject()}}' asc nulls first); result = group q_A by '{{cell(static_3.selection, 0, "Value").asObject()}}' full, q_B by '{{cell(static_3.selection, 0, "Value").asObject()}}' full, q_C by '{{cell(static_3.selection, 0, "Value").asObject()}}'; result = foreach result generate coalesce(q_A.'{{cell(static_3.selection, 0, "Value").asObject()}}', q_B.'{{cell(static_3.selection, 0, "Value").asObject()}}', q_C.'{{cell(static_3.selection, 0, "Value").asObject()}}', sum(q_A.'ActivityCount') as 'Proposal Revision', sum(q_B.'ActivityCount') as 'Proposal Revision Yes - Deal is Closed Won!', sum(q_C.'ActivityCount') as 'Initial Call', coalesce(grouping(q_A.'{{cell(static_3.selection, 0, "Value").asObject()}}'), grouping(q_B.'{{cell(static_3.selection, 0, "Value").asObject()}}'), grouping(q_C.'{{cell(static_3.selection, 0, "Value").asObject()}}'))) as 'grouping_{{cell(static_3.selection, 0, "Value").asObject()}}'; result = foreach result generate '{{cell(static_3.selection, 0, "Value").asObject()}}', 'Initial Call', 'Initial Call - Yes Schedule Meeting', 'Initial Call - Yes Schedule Meeting' / 'Initial Call' as 'Yes, Schedule Meeting', 'Presentation Meeting', 'Yes, Quote it - Presentation Meeting' , 'Yes, Quote it - Presentation Meeting' / 'Presentation Meeting' as 'Ratio - Yes Quote it','grouping_{{cell(static_3.selection, 0, "Value").asObject()}}'; summary = filter result by 'grouping_{{cell(static_3.selection, 0, "Value").asObject()}}' == 1; result = filter result by 'grouping_{{cell(static_3.selection, 0, "Value").asObject()}}' == 0; HERE IS SORTING BINDINGS THRU TOGGLES First binding'switch' {{cell(columnList_1.selection, 0, "sortPivots").asObject()}}result = order result by ('{{cell(columnList_1.selection, 0, "valu").asObject()}}' {{cell(sorter_1.selection, 0, "valu").asObject()}} nulls last); 2nd binding 'switch' {{cell(columnList_1.selection, 0, "sortMeasures").asObject()}} result = order result by ('{{cell(static_3.selection, 0, "Value").asObject()}}' {{cell(sorter_1.selection, 0, "valu").asObject()}} nulls last); result = union result, summary; SAQL resolves to …. q = load "0Fb5x000000ToKGCA0/0Fc5x000007ZFMjCAO"; q_A = filter q by 'Type_of_Call__c' == "Proposal Revision" && 'Proposal_Meeting_Result__c' in ["Maybe - Revise Proposal", "No - Create New Activity", "Yes - Deal is Closed Won!"]; q_B = filter q by 'Type_of_Call__c' == "Proposal Revision" && 'Proposal_Meeting_Result__c' == "Yes - Deal is Closed Won!"; q_C = filter q by 'Type_of_Call__c' == "Initial Call" && 'Result_of_Call__c' in ["CFA, Successful", "No, Successful", "No, Unsuccessful", "Yes, Schedule Meeting"]; q_A = group q_A by rollup('User_Division__c'); q_A = order q_A by ('User_Division__c' asc nulls first); q_B = group q_B by rollup('User_Division__c'); q_B = order q_B by ('User_Division__c' asc nulls first); q_C = group q_C by rollup('User_Division__c'); q_C = order q_C by ('User_Division__c' asc nulls first); result = group q_A by 'User_Division__c' full, q_B by 'User_Division__c' full, q_C by 'User_Division__c' ; result = foreach result generate c oalesce(q_A.'User_Division__c', q_B.'User_Division__c', q_C.'User_Division__c') as 'User_Division__c', sum(q_A.'ActivityCount') as 'Proposal Revision', sum(q_B.'ActivityCount') as 'Proposal Revision Yes - Deal is Closed Won!', sum(q_C.'ActivityCount') as 'Initial Call', coalesce(grouping(q_A.'User_Division__c'), grouping(q_B.'User_Division__c'), grouping(q_C.'User_Division__c')) as 'grouping_User_Division__c'; result = foreach result generate 'User_Division__c', 'Initial Call', 'Initial Call - Yes Schedule Meeting', 'Initial Call - Yes Schedule Meeting' / 'Initial Call' as 'Yes, Schedule Meeting'…etc ,'grouping_User_Division__c'; summary = filter result by 'grouping_User_Division__c' == 1; result = filter result by 'grouping_User_Division__c' == 0; --result = order result by ('User_Division__x' asc nulls last); result = order result by ('User_Division__c' asc nulls last); result = union result, summary; 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. 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'; |