Logo

Tag Archive for activities

The Model View Controller (MVC) Method of Building EA Dashboard Interactivity With Bindings – Part 2

In Part 1 we looked at the MVC method of building Einstein Analytics dashboards conceptually.  Now we are going to review specific examples and code to put it into practice.  First, let me say that I’m not sure everything that is accomplished is fully supported by the platform.  So know that it could potentially break at any point in time.  However, it has worked for a few years and while some examples for how you could use this can be accomplished via other methods, especially now with the ‘advanced editor’ via the UI – there are use cases where I’ve found this to be the only method that works.

A Few Use Cases

I have used this MVC design pattern in other programming languages so when I ran into roadblocks in trying to design a dashboard with certain functionality I tried it as a workaround and I was very happy when it ended up working!  I have used this in cases where we have a single widget (or multiple that leverage the same step/query) but need a variety of control from the end user.  Sure, if they just want to switch the grouping and/or the measure we would be fine.  But what if at the same time they needed to toggle the specific function that is being calculated on the fly (for example a windowing function).  These functions need to already have the stream organized and ‘grouped’ properly prior to the calculation.  Moreover, you need to specify that grouping and measure within the function.  On top of all of this, I’ve had to build dashboards that toggle between not only various groupings but completely different timeframes (hour, year-week, year-month-day, month) and use different summary calculations (avg, running total, etc.) and these all have to be specified within the function and the stream already ‘prepped’ with the proper information.

A Relatively Simple Example

What follows is a relatively simple example where we are only changing the measure, toggling between ActivityDate and CreatedDate and also toggling whether we are viewing the ‘actual’ count of records or a Moving Average period of 10, 15, 30, 45 or 60 days.  Just know that you could easily expand on this and add additional toggles for some of the use cases I mentioned above.  Once you set up the dashboard in MVC you have laid the groundwork to add additional functionality relatively easily.

 

In order to build this we need our visual components which are the widgets and the static steps for the Toggles.  These are fairly simple and can be input via the UI:

 

Then, you need to create a Controller Step that will use toggles to ‘feed’ code into the main step that feeds the chart.

Below is the full code for the Controller Step.  Essentially it takes a single ‘row’ (limit 1) of the dataset that we are using so we ‘know’ we will have at least one result and then we use a series of case statements in order to evaluate the user selections and output code based on those:

"ControllerStep": {

"broadcastFacet": true,

"groups": [],

"label": "ControllerStep",

"numbers": [],

"query": "q = load \"activity\";\nq = foreach q generate case when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"Actual\" then \"sum(count)\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"10 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"ActivityDate\").asString()}}\" == \"ActivityDate\" then \"avg(sum(count)) over ([-10..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"15 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"ActivityDate\").asString()}}\" == \"ActivityDate\" then \"avg(sum(count)) over ([-15..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"30 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"ActivityDate\").asString()}}\" == \"ActivityDate\" then \"avg(sum(count)) over ([-30..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"45 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"ActivityDate\").asString()}}\" == \"ActivityDate\" then \"avg(sum(count)) over ([-45..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"60 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"ActivityDate\").asString()}}\" == \"ActivityDate\" then \"avg(sum(count)) over ([-60..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"Actual\" then \"sum(count)\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"10 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"CreatedDate\").asString()}}\" == \"CreatedDate\" then \"avg(sum(count)) over ([-10..0] partition by all order by ('CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"15 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"CreatedDate\").asString()}}\" == \"CreatedDate\" then \"avg(sum(count)) over ([-15..0] partition by all order by ('CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"30 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"CreatedDate\").asString()}}\" == \"CreatedDate\" then \"avg(sum(count)) over ([-30..0] partition by all order by ('CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"45 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"CreatedDate\").asString()}}\" == \"CreatedDate\" then \"avg(sum(count)) over ([-45..0] partition by all order by ('CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day'))\" when \"{{coalesce(cell(static_1.selection, 0, \"Display\"), \"Actual\").asString()}}\" == \"60 Day MA\" && \"{{coalesce(cell(static_2.selection, 0, \"Value\"), \"CreatedDate\").asString()}}\" == \"CreatedDate\" then \"avg(sum(count)) over ([-60..0] partition by all order by ('CreatedDate_Year~~~CreatedDate_Month~~~CreatedDate_Day'))\" else \"avg(sum(count)) over ([-60..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'))\" end as 'WindowFunction', 'ActivityDate_Year' + \"~~~\" + 'ActivityDate_Month' + \"~~~\" + 'ActivityDate_Day' as 'ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day', count() as 'count';\nq = limit q 1;",

"receiveFacetSource": {

"mode": "all",

"steps": []

},

"selectMode": "single",

"strings": [],

"type": "saql",

"useGlobal": true,

"visualizationParameters": {

"parameters": {

"borderColor": "#e0e5ee",

"borderWidth": 1,

"cell": {

"backgroundColor": "#ffffff",

"fontColor": "#16325c",

"fontSize": 12

},

"columnProperties": {},

"columns": [],

"customBulkActions": [],

"header": {

"backgroundColor": "#f4f6f9",

"fontColor": "#16325c",

"fontSize": 12

},

"innerMajorBorderColor": "#a8b7c7",

"innerMinorBorderColor": "#e0e5ee",

"maxColumnWidth": 300,

"minColumnWidth": 40,

"mode": "variable",

"numberOfLines": 1,

"showActionMenu": true,

"showRowIndexColumn": true,

"totals": true,

"verticalPadding": 8

},

"type": "table"

}

},

Then, we use those outputs from the ‘ControllerStep’ in our primary step that feeds the visual chart on the dashboard:

"ActivityDate_Year_Ac_1": {

"broadcastFacet": false,

"groups": [],

"label": "ActivityDate_Year_Ac_1",

"numbers": [],

"query": "q = load \"activity\";\nq = filter q by date('ActivityDate_Year', 'ActivityDate_Month', 'ActivityDate_Day') in [dateRange([2018,4,28], [2018,8,12])];\nq = group q by ({{coalesce(cell(static_2.selection, 0, \"FirstGroup\"), \"'ActivityDate_Year', 'ActivityDate_Month', 'ActivityDate_Day'\").asString()}});\nq = foreach q generate {{coalesce(cell(static_2.selection, 0, \"Year\"), \"'ActivityDate_Year'\").asString()}} + \"~~~\" + {{coalesce(cell(static_2.selection, 0, \"Month\"), \"'ActivityDate_Month'\").asString()}} + \"~~~\" + {{coalesce(cell(static_2.selection, 0, \"Day\"), \"'ActivityDate_Day'\").asString()}} as {{coalesce(cell(static_2.selection, 0, \"2ndGroupOrder\"), \"'ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'\").asString()}}, count(q) as 'count';\nresult = group q by {{coalesce(cell(static_2.selection, 0, \"2ndGroupOrder\"), \"'ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'\").asString()}};\nresult = foreach result generate {{coalesce(cell(static_2.selection, 0, \"2ndGroupOrder\"), \"'ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'\").asString()}}, {{coalesce(cell(ControllerStep.result, 0, \"WindowFunction\"), \"sum(count)\").asString()}} as 'MovingAvg';\nresult = order result by ({{coalesce(cell(static_2.selection, 0, \"2ndGroupOrder\"), \"'ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day'\").asString()}} asc);\nresult = limit result 2000;",

"receiveFacetSource": {

"mode": "none",

"steps": []

},

"selectMode": "single",

"start": [],

"strings": [],

"type": "saql",

"useGlobal": false,

"visualizationParameters": {

"parameters": {

"autoFitMode": "keepLabels",

"showPoints": false,

"legend": {

"descOrder": false,

"showHeader": true,

"show": true,

"customSize": "auto",

"position": "right-top",

"inside": false

},

"axisMode": "multi",

"tooltip": {

"showBinLabel": true,

"measures": "",

"showNullValues": true,

"showPercentage": true,

"showDimensions": true,

"showMeasures": true,

"customizeTooltip": false,

"dimensions": ""

},

"visualizationType": "time",

"missingValue": "connect",

"dashLine": {

"measures": "",

"showDashLine": false

},

"timeAxis": {

"showTitle": true,

"showAxis": true,

"title": ""

},

"title": {

"fontSize": 14,

"subtitleFontSize": 11,

"label": "",

"align": "center",

"subtitleLabel": ""

},

"trellis": {

"flipLabels": false,

"showGridLines": true,

"size": [

100,

100

],

"enable": false,

"type": "x",

"chartsPerLine": 4

},

"fillArea": true,

"showActionMenu": true,

"showZero": true,

"measureAxis2": {

"sqrtScale": false,

"showTitle": true,

"showAxis": true,

"title": "",

"customDomain": {

"showDomain": false

}

},

"measureAxis1": {

"sqrtScale": false,

"showTitle": true,

"showAxis": true,

"title": "",

"customDomain": {

"showDomain": false

}

},

"valueType": "none",

"theme": "wave",

"applyConditionalFormatting": true,

"drawArea": {

"measure": "",

"showDrawArea": false,

"bounding1": "",

"bounding2": ""

}

},

"type": "chart"

}

},






I didn’t do it here but for a more polished look you can even update all display names on the chart axis with the proper string based on the user selection.

Please let me know if you have had success with this.  Please let me know if you have a better solution!  Honestly this is a bit of a hack that I’ve come up with but it has saved me countless times!  Every time I think I can do it via normal binding functionality I run into gaps and limitations due to issues with having to already have the stream prepped for the function or specifying the measure and grouping in the function which you cannot do with normal binding syntax.  This has been a lifesaver and I hope it is helpful to you.  Until next time, keep riding the Wave (or Einstein Analytics)!  Once Wave Always Wave!

Convert Decimal Time Into Hours and Minutes – HH:MM – Wave Einstein Analytics

Salesforce Quick Tips

Thought I would write about this Analytics Quick Tip as I have found zero information related to this online and I would imagine it would be a pretty common use case.

The Problem With Decimal Hours

Often you have ‘duration’ type measures stored as ‘number of seconds.’  Think of the duration of a service ticket from one stage to the next, or the duration of a sales call.  Many times you want to aggregate all of these together for a particular user or particular day.  Once you’ve aggregated them together, you can easily convert these into decimal hours by dividing by 60 to arrive at # of minutes and 60 again to arrive at # of hours (divide by 3600 once to shortcut this step).  The trouble is then you have a confusing metric of decimal hours.  3.45 hours does not equal 3 hours and 45 minutes.  It is closer to 3 hours and 30 minutes!  4.87 does not equal 4 hours and 87 minutes!  No matter how many times you try and educate your users on decimal hours it is confusing for them to discuss with their teams and properly rank and compare them to each other.

A SAQL Solution For Converting Decimal Hours to HH:MM

I think it’s helpful to break each step down into its component part before we get into the code of how we are going to accomplish this.

We want to take 4.87 and convert to 4:52.

  1. First we want to take out the # preceding the decimal point as it is already a valid # of hours. (4)
  2. Then we want to add a colon to separate hours from minutes. (4:)
  3. Then we want to take the remaining number post decimal point and multiply by 60 (this number will be formatted to only 2 digits and placed after the colon). (4:52 – we arrive at this by taking the entire 4.87 and subtracting the truncated number of 4 which leaves .87 and then we multiply that by 60)

Simple corresponding example below without formatting and where ‘decimalhour’ is already in decimal hours.  You would just replace ‘decimalhours’ with your duration measure and divide by 3600 if converting from seconds.

1. trunc(sum('decimalhours'))
2. + ":" +
3. sum('decimalhours') - trunc(sum('decimalhours')) * 60

Here is an example of the full saql w/ formatting.

 number_to_string(trunc(sum('decimalhours')),"#") + ":" + number_to_string(((sum('decimalhours')) - (trunc(sum('decimalhours')))) * 60,"0#") as 'HH:MM'

Now we have a very easy to read table in hours and minutes!

Owner Total Time
Bruce Kennedy 4:36
Catherine Brown 4:02
Chris Riley 3:55
Dennis Howard 3:29

The only issue I’ve found is when someone is right on the hour, say 4:00 and it only displays a single zero. I’ve solved for this with a case statement as in all of my use cases we are 5 hours and below so I only need 5 case statements.  This is very easy to do.  I had first tried replace but it would then replace all scenarios where there is a zero after the colon.  I’m sure there is a more elegant solution but I have not had the chance to spend very much time on this piece and the case statement solved my particular situation.

Did this solution help you?  Do you have another method to solve the same problem?  Let me know in the comments.  I would love to hear from you!

How to Close or Complete Event Activities + Salesforce Bug!!

Salesforce Events – Closed or Open?

Do you know what determines whether a Salesforce Event Activity displays in the “Open Activities” related list or the “Activity History” related list?  Let’s start with an event activity with a start/end date in the past:

Event Details

This record displays on the Account detail page within the “Activity History” related list, not the “Open Activities” section.

account activity history related list

What about when you are selecting to view Open or Completed Activities within a Salesforce Report?

report show open or completed activities

 

Events Are Automatically Completed/Closed

Salesforce documentation indicates “All events will automatically be moved to the Activity History the day following the scheduled event date.”  In other words, unlike tasks where they are manually marked “complete” – events are automatically completed after the scheduled date has past.

I found this very interesting because I’ve seen events appear “completed” on the same day as the scheduled start/end date.

A Warning About Utilizing Report Data

Within Salesforce Reports, there is a field labeled “Closed.”  This field indicates whether the task or event record has been completed.  This isn’t an actual field on the record and cannot be found within the database utilizing a SOQL query.

event soql db details no closed field

The issue with Events is that the same exact “Closed” field can display different results depending on whether it is a standard or custom report type.

The same record above that displays in the “Completed Activities” related list displays as “Closed” = True in the standard activity report type:

Standard Report Type Closed Event

However, when using a custom report type the same record displays as “Closed” = False.

Custom Report Type Open Event

Come on Salesforce!  This can’t be by design, can it?!  I can only assume that because it’s really a ‘faux field’ and the report is determining whether the event is closed or not that this logic was only applied to the standard report types.

This can be dangerous particularly if you were using this field in a CRT as filter criteria or using this field to aggregate – sum/avg/etc across all activities.

What are your thoughts or issues with Events?  Have you created a custom field to let users manually indicate whether they have completed the activity?  There are numerous ideas on the Success Community regarding the way Events are marked complete.  Here’s one.  Please let me know your thoughts in the comments or on twitter @SFDC_r – I would love to hear from you!