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
0 Comments
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()}}; Great article on CRMA vs. Native Salesforce reporting.
https://marktossell.com/2022/10/22/why-do-you-need-crma/
So client wants 2 snapshot dates populated . Snap1 is the most recent snapshot--easy enough to accomplish by a query sorted in ascending order which takes the most recent one and puts it in snap1list selector. The 2nd snapdate is 1 year back. This can be done by populating the 'start' attribute in dashboard JSON with a results from query 'q_oneYrBack_1'.
"start": "{{cell(q_oneYrBack_1.result,0,\"sd2\").asString()}}" "q_oneYrBack_1": { "broadcastFacet": true, "groups": [], "label": "q_oneYrBack", "numbers": [], "query": "q = load \"zds_completeOpptys_v2\";\nq = filter q by date('SnapshotStamp_Year', 'SnapshotStamp_Month', 'SnapshotStamp_Day') in [\"365 days ago\"..\"365 days ago\"];\nq= group q by 'SnapshotStamp';\nq = foreach q generate 'SnapshotStamp' as 'sd', substr('SnapshotStamp',2,10) as 'sd2';", "receiveFacetSource": { Sd2 resolved to text in yyyy-MM-dd format (hence the 10 character string in the substr function). AS a followup to the March 3, 2021 blog regarding creating a flex-grid where the pivots are dynamic.. sorta like OLAP. Below is code for using saql as opposed to aggregate. Here is a snip of the custom query. Pay attention to the saql column and notice the ' ' on the API names. After it is the snip of the saql query.
q = load \"x_MasterSalesData\";\nq = group q by ({{column(static_2.selection,[\"saql\"]).asObject()}});\nq = foreach q generate {{column(static_2.selection,[\"saql\"]).asObject()}}, count() as 'count';\n" so let's say you have 'due Date' and you want to see it in week-year format.. ex "2-2022" or 54 - 2022. One of the ways it can be achieved ,in addition to dashboaard xmd? is to use multi datastream. In example below, the q2 datastream highlights the ww-yyyy format.
q = load "TaskWithDetails"; q = group q by ('ActivityDate_Year', 'ActivityDate_Week'); q = foreach q generate 'ActivityDate_Year' + "~~~" + 'ActivityDate_Week' as 'ActivityDate_Year~~~ActivityDate_Week', 'ActivityDate_Week' + "~~~" + 'ActivityDate_Year' as 'ActivityDate_Week~~~ActivityDate_Year',count() as 'count'; q = order q by 'ActivityDate_Year~~~ActivityDate_Week' asc; q2 = foreach q generate 'ActivityDate_Week~~~ActivityDate_Year' as 'ActivityDate_Week~~~ActivityDate_Year', count() as 'count'; There are several key differences between dataflows and recipes and I will be going over a few of them.
#1) slight variation in case statements: Dataflows: case when Grade_High__c == “13th ” then 13 when Grade_High__c == “14th ” then 14 else 0 end Recipes: case when Grade_High__c = ‘13th’ then 13 when Grade_High__c = ‘14th’ then 14 else 0 end #2) Creating outer/inner joins more straightforward in recipes using selection buttons. Be careful to distinguish between ‘lookups’ and left joins. (hint: lookups return the first instance of matching rows while left outer returns all.. #3) Data preview INSIDE the recipe reduces the need to create intermediate datasets to verify contents of dataset as it goes thru the different transformations. |
|