Einstein Analytics: Compare Table functions

Compare Table is another powerful and easy to use feature in Einstein Analytics to meet your business requirements, you do not need to manually construct the SAQL or edit with JSON. It allows you to aggregate data into a table (or chart) based on formulas defined.

You can use familiar SAQL syntax to create your own formula for a column of multiple columns in a table or visualize it to a chart. 

On top of manually building custom formula with SAQL, Einstein Analytics also provide defined windowing functions to analyze data across rows.

Sliding Window

Applies an aggregate function to the current row with respect to a configurable range of rows.

- Column: source data (a column)

- Function: Average, Sum, Min, Max

- Start: start row to analyze, -1 mean 1 previous row from the current row

- End: end row to analyze, 0 mean current rows, 1 mean 1 row after current row

- Reset Group: if only you have more than 1 grouping, you can reset by 

Let us see a sample:

This function compares the current value with 1 previous row (start = -1, and end = 0), then get the Max value. 

Once saved, we will see the formula

max(A) over ([-1..0] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day'))

Now, let us add Account Name as 2nd level grouping without adding Reset Group

Because there are 3 accounts on 13-May-2017, the row explodes to 3 lines, but the logic still the same, which is comparing max value between the current and previous row. In this change, the formula will not change.

Now, let us add Reset Group = Opty.CloseDate

The formula will be changed by adding Account Name (in this sample the API name is "Name")

max(A) over ([-1..0] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day','Name'))

Because Close Date added as Reset Group, windowing functions only compare within the same date.

Percentage of Group

Calculates the percentage each row is of its group total, or of the grand total. The percentage only applicable for data shown in the table, NOT for the whole data in dataset (if you apply filter).

The formula: A/sum(A) over ([..] partition by all)

The function can be reset on a grouping defined in the table, this is the same with Sliding Window, you need to have minimum 2 grouping to apply Reset Group.

The formula: A/sum(A) over ([..] partition by 'Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day')

Rank Within Group

There are 4 functions offered by Rank Within Group:

• Rank: assigns rank based on order. Repeats rank when the value is the same, and skips as many on the next non-match
• Dense Rank: same as rank() but doesn’t skip values on previous repetitions.
• Cumulative Distribution: calculates the cumulative distribution (relative position) of the data in the reset group
• Row-number: assigns a number incremented by 1 for every row in the reset group.

Formulas for each function:

• Rank: rank() over([..] partition by all order by A desc)
• Dense Rank: dense_rank() over([..] partition by all order by A desc)
• Cumulative Distribution: cume_dist() over([..] partition by all order by A desc)
• Row Number: row_number() over([..] partition by all order by A desc)

The Order menu determines the direction of ranking based on the values being ranked. Ascending ranks the lowest value as number 1, while descending ranks the highest value as number 1. Same as Sliding Window and Percentage of Group function, we can add Reset Group in Rank Within Group.

Period Over Period

To use Period Over Period, the table must be group by Date field. Period Over Period function compare periods of time to calculate changes in values, for example: year-over-year, quarter-over-quarter, month-over-month, week-over-week, or day-over-day.

Then, we have option to show the result as: % Change or Unit Change. 

Formula for % Change

(A - sum(A) over ([-1..-1] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day')))/(sum(A) over ([-1..-1] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day')))

Formula for Unit Change

A - sum(A) over ([-1..-1] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day'))

Period Over Period do not offer Reset Group.

Change from Previous

Compares the value of the current row with that of the previous row and calculates the difference. This function similar with Period Over Period, but you do not need to group by Date. 

Change from Previous offer Reset Group function and similar with Period Over Period, we have option to show the result as: % Change or Unit Change.

Formula for % Change

(A - sum(A) over ([-1..-1] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day')))/(sum(A) over ([-1..-1] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day')))

Formula for Unit Change

A - sum(A) over ([-1..-1] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day'))

Above table looks very similar with Period Over Period, but Change from Previous can be implemented to any grouping, not just Date over a period.

Running Total

Calculates the total value of the current row summed with all previous rows. 

Running Total formula without Reset Group: sum(A) over ([..0] partition by all order by ('Opty.CloseDate_Year~~~Opty.CloseDate_Month~~~Opty.CloseDate_Day'))

Running Total also offer Reset Group if you have more than 1 grouping in table. Let us see in sample with and without reset Group:

Reference:

• Add Custom Formulas to Columns
• Window Functions Available in the Compare Table
• Windowing Functions

Einstein Analytics: computeExpression return Date

In some scenarios, we need to return a Date field from computeExpression node. 

Date value can be null

sample:

case when ClosedDate_sec_epoch > date_to_epoch(now()) then toDate(ClosedDate,"yyyy-MM-ddTHH:mm:ss.000Z") end

or

case when ClosedDate_sec_epoch > date_to_epoch(now()) then toDate(ClosedDate_sec_epoch) end

Date or Date/Time

For a Numeric field, Precision and Scale are required, while for a Date field, Date Format is required. Einstein Analytics by default will "explode" a Date field into:

- Year

- Quarter

- Month

- Week

- Day

- Hour

- Minute

- Second

- Epoch days

- Epoch seconds

Let us see some samples of computeExpression returning Date type. In this sample, ClosedDate is a Date/Time field in Salesforce:

(1) SAQL Expression: toDate(ClosedDate,"yyyy-MM-ddTHH:mm:ss.000Z")

Date Format: yyyy-MM-ddTHH:mm:ss.000Z

(2) SAQL Expression: toDate(ClosedDate_sec_epoch)

Date Format: yyyy-MM-ddTHH:mm:ss.000Z

(3) SAQL Expression: toDate(ClosedDate,"yyyy-MM-ddTHH:mm:ss.000Z")

Date Format: yyyy-MM-dd

(4) SAQL Expression: toDate(ClosedDate,"yyyy-MM-dd")

Date Format: yyyy-MM-ddTHH:mm:ss.000Z

this will cause error because date format with HH:mm:ss, while toDate() format string parameter does not have.

(5) SAQL Expression: toDate(ClosedDate_sec_epoch)

Date Format: yyyy-MM-dd

(6) SAQL Expression: ClosedDate

Date Format: yyyy-MM-dd

this will cause error, we can't copy directly even it is a date field 

(7) SAQL Expression: now()

Date Format: yyyy-MM-dd

this is okay, it will return UTC date and time

As the screenshot above, the date format determines how the values stored in Einstein Analytics. But, let us drill further:

Even hour, minute, and second are not added in the date format then not shown in the date field, Einstein Analytics still maintain hour, minute, and second in the exploded fields.

yyyy-MM-ddTHH:mm:ss.000Z is similar with yyyy-MM-ddTHH:mm:ss.SSSZ and similar with yyyy-MM-dd'T'HH:mm:ss.SSS'Z'

Now, let us try to use Date field from Salesforce, instead of Date/Time field. CloseDate here is a Date field in Salesforce:

(1) SAQL Expression: toDate(CloseDate,"yyyy-MM-dd")

Date Format: yyyy-MM-ddTHH:mm:ss.000Z

(2) SAQL Expression: toDate(CloseDate_sec_epoch)

Date Format: yyyy-MM-ddTHH:mm:ss.000Z

(3) SAQL Expression: toDate(CloseDate,"yyyy-MM-ddTHH:mm:ss.000Z")

Date Format: yyyy-MM-dd

this will cause error

(4) SAQL Expression: toDate(CloseDate_sec_epoch)

Date Format: yyyy-MM-dd

(5) SAQL Expression: toDate(CloseDate,"yyyy-MM-ddTHH:mm:ss.000Z")

Date Format: yyyy-MM-ddTHH:mm:ss.000Z

this will cause error

(6) SAQL Expression: toDate(CloseDate,"yyyy-MM-dd")

Date Format: yyyy-MM-dd

Because there is no time in the source field, so all HH mm ss will be 00.

In summary:

• for SAQL Expression, it is easier to use toDate(xxxx_sec_epoch) 
• for Date Format, use yyyy-MM-ddTHH:mm:ss.000Z for Date/Time, or use yyyy-MM-dd for Date field

Side Note: can we refer a field in computeExpression within the same note?

Yes, as long as the field referred is located above (in order) from the field call it, see screenshot below, CloseDate_2 can use CloseDate_1, but not CloseDate_4 and CloseDate_6.

Einstein Analytics: Hidden Step

Hidden step probably not the official term in Einstein Analytics, but you probably heard when having a conversation with Einstein Analytics experts with many years of experience, furthermore, Salesforce no longer calls it "step", but now it calls "query".

So, what is the hidden step? it is a query that used to support other queries, but not directly uses for widgets. The query can be in compact form, Salesforce Direct, SAQL, and SOQL.

As mentioned above, we can use a hidden step to support other queries, in this case, usually, we use "result" binding, remember selection binding will be trigger when user change/select something.sim

Sample SOQL query:
 "QueryLoginUser_1": {
                "groups": [],
                "numbers": [],
                "query": "SELECT Name,Id FROM User Where Name = '!{User.Name}'",
                "selectMode": "single",
                "strings": [],
                "type": "soql"
            }

use it in a "real" query:
         "filters": [
                        [
                            "OwnerId",
                            "{{cell(QueryLoginUser_1.result,0,\"Id\").asString()}}",
                            "in"
                        ]
                    ]


Sample SAQL query:
q = load "Quote_History";
q = filter q by 'StatusChange' == "Created to In Progress";
q = group q by all;
q = foreach q generate avg('Cycle_Time') as 'avg_Cycle_Time';
q = foreach q generate avg_Cycle_Time as 'ave_seconds', floor(avg_Cycle_Time / 3600) as 'hours', floor((avg_Cycle_Time / 60) - (floor(avg_Cycle_Time / 3600) * 60)) as 'minutes';
q = foreach q generate ave_seconds as 'ave_seconds', hours as 'hours', minutes as 'minutes', floor(ave_seconds - ((3600 * hours) + (60 * minutes))) as 'seconds';
q = limit q 2000;

If you open in JSON, it would become
"cycle_time_1": {        
"query": "q = load \"Quote_History\";\nq = filter q by 'StatusChange' == \"Created to In Progress\";\nq = group q by all;\nq = foreach q generate avg('Cycle_Time') as 'avg_Cycle_Time';\nq = foreach q generate avg_Cycle_Time as 'ave_seconds', floor(avg_Cycle_Time / 3600) as 'hours', floor((avg_Cycle_Time / 60) - (floor(avg_Cycle_Time / 3600) * 60)) as 'minutes';\nq = foreach q generate ave_seconds as 'ave_seconds', hours as 'hours', minutes as 'minutes', floor(ave_seconds - ((3600 * hours) + (60 * minutes))) as 'seconds';\nq = limit q 2000;"}

use it in a "real" query
"text": "{{column(cycle_time_1.result, [\"seconds\"]).asObject()}}",



Reference:

• Einstein Analytics Tips – Hiding columns in Tables Dynamically
• Einstein Analytics: Result Binding

Einstein Analytics: Parent with and without Child dataflow

This is regarding the parent-child relationship.

So here the needs:

• if 0 child found --> 1 row
• if 1 child found --> 1 row + child fields
• if >1 child found --> number of child row + child fields 

Is there a way to achieve the following in Einstein Analytics dataflow?

In Salesforce, this is pretty easy using a report type:

But, Einstein Analytics is a different story. The granular level should come from an object. However, we always can find ways to get things done.

So here is the solution by Roman Michalik from Salesforce:

• It's starting at Child level and giving you one row per child. 
• Then it's checking for additional parents and adding those parents to the list (with blank child fields).

Here is the sample JSON for dataflow.

Note: to use this dataflow, Parent ID must be unique.

Another option is to use Recipe with Left Join.


Reference:

• Advanced Data Modeling 1: Parent With or Without Children
• Left Join

myTrailhead Introduction

My experience and journey with myTrailhead

Rebecca “Becky” Sweetman, Global Business Operations Manager, Adstream
Salesforce Singapore User Group and SG Women in Tech member
www.linkedin.com/in/rebeccasweetmanSG
Twitter @BeckySweetman3

What is myTrailhead and how it is different from Trailhead?

myTrailhead is a modern gamified online learning enterprise platform tailored for your company that allows you to leverage the power of Trailhead. It allows you to create personalized self-paced integrated learning journeys, efficient and consistent onboarding - reducing ramp-up time to accelerate time to revenue - increases employee productivity, drives adoption of resources to maximize their investment, and cultivates a developmental company culture. It isn’t just to train Salesforce tools either, as we use it for internal training of our own products and services, soft sales skills, etc. I also use it to manage  Salesforce releases and introduce the tools that matter to them. 

Users can proudly show off badge-swag on their profile and analytics can show the best and most diligent learners. Internal badges will only be visible inside myTrailhead, not on the public site.

You will require at least a standard Salesforce user license (sales cloud, service cloud or platform) and then an added myTrailhead license. The charge comes per user as a package and that is the only fee - except if want to add on Premier Support. The advertised price is $25 USD per user per month annual billing. $2/login or $5/member for Customers and Partners

myTrailhead uses a separate personal domain to Trailhead for user access and uses a connector user in Salesforce to send analytical data to Trail Tracker. Users login with Salesforce credentials to this domain but auto-links with any pre-existing Trailblazer.me so everything is retained in one place and you will still retain and view your ranks, points, and badges in one profile.

myTrailhead Features

myTrailhead comes with its own set of features that work together with the Salesforce platform.

Trailmaker is the tool to create, preview and release the content. You can have as many users access content creation as you wish as this is not limited and just a matter of permission sets.

myTrailhead has the ability to embed videos, images, and tables, and you can easily copy/paste your content. It also allows you to add quiz questions. For anyone familiar with Trailhead – it is basically, the same format.

You can use Trailhead content in Trailmixes to publish inside trails too, but these cannot be modified further. Trailmixes can also link to external content hosted elsewhere to bring this into your training journey.

Trailmaker allows you to preview your content and send it to others to view prior to publish and you can easily add, delete or update the content at any point in a separate Releases tab. In this tab, once you easily upload your content to the Release it will inform you of any errors (cannot publish with these) or warnings (not ideal but will still allow you to publish) prior to publication. 

Same as in Trailhead, users can track their own way through the modules and trails with a countdown of time left until completion. 

You or anyone you give permission rights to can add your own company logo and banner to myTrailhead and add your brand’s color so it feels familiar and personalized for you. 

You will need to create your own badge art which can be stored in the Documents object (accessible in Classic only right now). You can assign modules (badges) and Trailmixes. This enables it to come upfront and center on their myTrailhead home page and once complete will show in your reports.

Trail Tracker is your tool to track completion for users, and it syncs daily at a time of your choice. It also comes with a set of standard Reports and Dashboards which you can customize. Examples include Total Number of Badges Assigned, Number of Badges Awarded This Month and a Leaderboard of Badges and Points.

You can also add the badges component to Profiles for users and their managers to see their progress.

Finally, you can use the power of Process Builder to set up and automate an onboarding or learning journey for your users. 

Your users can also access myTrailhead from the Trailhead Go app for training on the run, currently available for iOS devices only.

Tips and Recommendations

This tool works perfectly for our global workforce and their training needs. However, here are a few tips and gotchas I wished I had known at the start of my journey configuring and using myTrailhead.

You do not need an implementation partner. It is very easy to enable and set up myTrailhead. As soon as your contract is signed you just need to wait for an email from them directly. You also need to install Trail Tracker with the use of a connector user. The time resource is in the content creation itself and adapting it for the system.

As previously mentioned, permissions are all done through permission sets in Salesforce. You can give certain users permission to also preview content prior to go-live for that particular trail or module and permission to add or change your branding. No extra cost or limits on numbers of each of these.

Onboarding them I found a little more complex.  The users don’t automatically get an alert when they are assigned permissions of course, nor to introduce them to the myTrailhead system and if they are not already set up in Trailhead, you need to wait for them to login and add their own profile before you can assign anything or the assignment sync will fail for them. You also will not know they have created this profile unless they tell you. The best steps I found for this process is: 

1. Offer users to login to your myTrailhead link
2. Have them login with their own Salesforce credentials to enable the connection to both systems (not to login with just their email or will just set up a public profile and won’t link the permissions).
3. Set up their own profile (if they do not already have a Trailhead profile)
4. Then inform you so you can assign to them with Trail Tracker if applicable

You can assign in bulk using Trail Tracker but if one person is not set up it will fail all. Refer to your included “Debug Log” report to view any sync errors. 

myTrailhead is not a traditional LMS, it is a learning experience system LXP, so you cannot use LMS files and may have problems shifting from a different LMS system, but it does hold videos, tables, images and links to other hosted external content. Because of this, it is best to get executive buy-in straight away and make the content creators realize this is not a system to throw just bullet points and PowerPoints, this is a journey and the content should be in the format of the story with examples, not short form. I find the best thing to do is have them set up and undertake a module in Trailhead to gather what the journey will be like.

myTrailhead is not a high maintenance system for admins, but the content creation often takes a lot of time, so prepare the team for this resource outlay. Also, have someone available to edit it for voice and spelling/grammar and make sure objectives given to meet the content and questions. Also, schedule a regular session to update and add new content so it does not become stale.

For content creation, you need to use your own version control or content management system such as Quip or Google docs to keep up with changes and involve others then update content in myTrailhead. This is not provided with myTrailhead, although you can download ‘backpacks’ of your files for backup and make changes and re-upload.

It is easy to copy/paste directly into Trailmaker but there are only one font and 2 sizes (header and body text).

API names for each unit, module, and trail need to be unique across all of Trailhead or errors may show.

Badge art needs to meet certain specs and as you use a URL to add it to Trailmaker if the URL is not shortened it can send an error and not sync. Use Bitly or similar to shorten the link. There is a myTrailhead Badge Style guide here: https://success.salesforce.com/_ui/core/chatter/groups/GroupProfilePage?g=0F93A0000009W5F&fId=0D53A00004Y3PYH

If you deactivate a user and reactivate them, all their badges will still be retained.

You can set internal Trailmixes which will not be seen in public Trailhead, however, you cannot yet set controls around who can see or do these in myTrailhead – so, for example, anyone with a myTrailhead license in your company can search for or find a module and do the training. They will come up in reports that they have completed it, whether assigned or not.

Make use of automation like Process Builder and add badge recommendations to the in-app guidance.

It is still early days in myTrailhead, only launched last year, so flexibility in the tool can appear to be a bit limited. For example, you cannot change role, level, tags, products, etc. in the modules, but these are on the roadmap. There is no Playground as they have on Trailhead, but it is on the roadmap. Also, Trail Tracker only syncs once daily at this stage but looking forward to when we can get it on demand.

Reports will pull all Trailhead and myTrailhead data to reports for you. Filter on namespace or URL subdomain value to get internal-only data. For managers to see the report data you need to give them at least read access to Trailhead-related objects and user data.

Further Information and Training

Information: https://trailhead.salesforce.com/mytrailhead

Search for “myTrailhead Basics” Trailhead Trail

MyTrailhead YouTube learning video series: https://www.youtube.com/playlist?list=PL3I_4yQhlmQA4dh3E5FuOBO7IcJr_MUYJ

Quip doc for myTrailhead and Trail Tracker: https://www.quip.com/IthKAuemOn5O/myTrailhead-External-FAQs

Separate logging of support cases (although some cases need to route through usual Salesforce help) https://trailhead.salesforce.com/help

myTrailhead Community Group: https://success.salesforce.com/_ui/core/chatter/groups/GroupProfilePage?g=0F93A0000009W5F

This is just the beginning of our journey with myTrailhead. I am always keen to talk to others and hear tips and recommendations from those with similar or other experiences. Please feel free to reach out to me on LinkedIn, Salesforce Community or Twitter.

Happy Trails! 

Becky.

Salesforce: EmailMessage object

When Enhanced Email is enabled, Salesforce will create EmailMessage object. Emails sent from Salesforce are saved as Email Message records and Task records. There is a link from the Email Message record to a Task record, which is ActivityId field.

If you use Outlook panel (and not enable EAC), you can "Log Email" manually to Salesforce for email received and sent out. Both emails received and sent will be stored both as EmailMessage (prefix 02s) record and Task record (prefix 00T).

How to differentiate email send from Salesforce and manually logged from an email client?
You will not find any difference on the Task object, both Type and TaskSubType will populate with "Email". But there are some differences in Email Message object. Check out this query:
SELECT Id, ActivityId, FromAddress, ToAddress, FromName, IsClientManaged, MessageIdentifier, Subject, TextBody FROM EmailMessage

Row 1,4,5 - email manual log from Outlook
Row 2,3 - email sent from Salesforce

As you can see, IsClientManaged and MessageIdentifier are different.

Note: using My Email to Salesforce service (BCC) will not create Email Message records, but only Task.


Email in Lightning activity component
All email logged to Contact or Lead will be shown in the Lightning under activity component.

For manually logged email to Salesforce, click the email will open EmailMessage record, you will be able to Reply All, Reply, or Forward the email from that panel. Hover mouse over the email will show the URL where it will be landed, example: https://mydomain.lightning.force.com/lightning/r/02s2H00001BMfIgQAL/view

For email from BCC email services, click the email will open Task record, there is no option to reply, reply all or forward as the record is a task. Hover mouse over the email will show the URL where it will be landed, example: https://mydomain.lightning.force.com/lightning/r/00T2H00007KIQvsUAH/view

For EAC email, click the email will open the email in that panel, you also able to Reply All, Reply, or Forward the email from that panel. The system also shows if the email is shared with everyone or a group or not shared. Hover mouse over the email will not show any URL, click "View full email" will open the full email as email stream.

Reference:

• EmailMessage