Customizing the Sales Wave Dashboard for Pipeline Trends

Sales Wave provides a set of dashboards that serve most business’ needs, but there will always be businesses that want additional requirements or customization,

 

Out of the Box vs. Customized

Two common requests are additional custom filters, and changes to date ranges. Most people are probably thinking, “This shouldn’t be a problem, I’ve been using Wave for a while and have built plenty of dashboards using SAQL”.

Editing JSON Workflow

In case you don’t already know the drill:

  1. Open the dashboard you want to modify
  2. Save as a new copy
  3. Find the step you want to edit
  4. Go to the JSON editor
  5. Make your changes
  6. Save & Done

As you can see, modifying the Sales Wave dashboard is pretty straightforward, simply by referencing the Dashboard JSON and SAQL help pages. That is, until you come across the Pipeline Trending dashboard! Specifically, the step that drives the  waterfall chart.

 

It’s a pretty overwhelming query and to understand it all would take some time (but stay tuned for a future blog post). To help shortcut the process, Justin Kim from our ASG team has kindly outlined the areas that you need to modify for custom filters and date ranges.

Filters

To add or modify the filters, you would have to do the same as usual by clipping a new lens or create a new static step and add the widget to the layout. To add/edit bindings in the query, find the section after the load statement.

Original query:

x1 = load "pipeline_trending";
x1 = filter x1 by 'Opportunity.Owner.Name' in {{ selection(Opportunity_Owner_Name_3) }};
x1 = filter x1 by 'Opportunity.Owner.Role.Name' in {{ selection(Opportunity_Owner_Role_3) }};
x1 = filter x1 by 'Opportunity.Account.Industry' in {{ selection(Account_Industry_12) }};
x1 = filter x1 by 'Opportunity.Account.BillingCountry' in {{ selection(Account_BillingCountry_11) }};
START_REAL = filter x1 by date('ValidFromDate_Year', 'ValidFromDate_Month', 'ValidFromDate_Day') in [{{ no_quote(value(field(selection(step_time),'Start_ValidFrom'))) }}];
START_REAL = filter START_REAL by date('ValidToDate_Year', 'ValidToDate_Month', 'ValidToDate_Day') in [{{ no_quote(value(field(selection(step_time),'Start_ValidTo'))) }}];
...

Query with additional filters:

x1 = load "pipeline_trending";
x1 = filter x1 by 'Opportunity.Owner.Name' in {{ selection(Opportunity_Owner_Name_3) }};
x1 = filter x1 by 'Opportunity.Owner.Role.Name' in {{ selection(Opportunity_Owner_Role_3) }};
x1 = filter x1 by 'Opportunity.Account.Industry' in {{ selection(Account_Industry_12) }};
x1 = filter x1 by 'Opportunity.Account.BillingCountry' in {{ selection(Account_BillingCountry_11) }};
x1 = filter x1 by 'Opportunity.Account.Market' in {{ selection(Market_Filter_Step) }};
START_REAL = filter x1 by date('ValidFromDate_Year', 'ValidFromDate_Month', 'ValidFromDate_Day') in [{{ no_quote(value(field(selection(step_time),'Start_ValidFrom'))) }}];
START_REAL = filter START_REAL by date('ValidToDate_Year', 'ValidToDate_Month', 'ValidToDate_Day') in [{{ no_quote(value(field(selection(step_time),'Start_ValidTo'))) }}];
...

In the above example, a filter has been added for the Account Market.  Note that this field is not included in the datasets by default, so the ELT Dataflow must be modified to include this new field. For help on configuring the ELT Dataflow, you can refer to the Analytics Cloud Help page.

Date Ranges

Since the date filters are already in the query, the modifications are not actually made in the waterfall query. You can simply find the corresponding step to the date filter widget:

Static Date Range Template:

{
"display": "[start] to [end]",
"value": {
"Start_ValidFrom":      "dateRange([1, 1, 1], [start - 1 day])",
"Start_ValidTo":        "dateRange([start], [9999, 1, 1])",
"End_ValidFrom":        "dateRange([1, 1, 1], [end])",
"End_ValidTo":          "dateRange([end + 1 day], [9999, 1, 1])",
"Update_ValidFrom":     "dateRange([1, 1, 1], [start - 1 day])",
"Update_ValidTo":       "dateRange([start], [end])",
"Update_End_ValidFrom": "dateRange([start], [end])",
"Update_End_ValidTo":   "dateRange([end + 1 day], [9999, 1, 1])",
"CloseDate_1":          "dateRange([start], [end])",
"CloseDate_2":          "\"[start]\"..\"[end]\""
}
}

Static Date Range Example:

{
"display": "08-12-2015 to 08-19-2015",
"value": {
"Start_ValidFrom":      "dateRange([1, 1, 1], [2015, 8, 11])",
"Start_ValidTo":        "dateRange([2015, 8, 12], [9999, 1, 1])",
"End_ValidFrom":        "dateRange([1, 1, 1], [2015, 8, 19])",
"End_ValidTo":          "dateRange([2015, 8, 20], [9999, 1, 1])",
"Update_ValidFrom":     "dateRange([1, 1, 1], [2015, 8, 11])",
"Update_ValidTo":       "dateRange([2015, 8, 12], [2015, 8, 19])",
"Update_End_ValidFrom": "dateRange([2015, 8, 12], [2015, 8, 19])",
"Update_End_ValidTo":   "dateRange([2015, 8, 20], [9999, 1, 1])",
"CloseDate_1":          "dateRange([2015, 8, 12], [2015, 9, 30])",
"CloseDate_2":          "\"2015-08-12\"..\"2015-08-19\""
}
}

With that, hopefully you were are able to fast track and customize the Pipeline Trending dashboard with some of the common asks. For more details and additional customizations like Dynamic Date Ranges and Bucket Labels, you can refer to the SAQL Best Practices Guide.

For more Analytics Cloud developer resources please go to https://developer.salesforce.com/page/analytics.

About the Authors

Terrence Tse (also known as Terry) is a Solution Architect dedicated to providing solutions in accelerating & optimizing the selling and implementation of Wave.

Justin Kim is a data scientist where he works with customers to make them successful on Wave and provides advanced SAQL solutions.

Terry & Justin are members of the Analytics Cloud’s ASG team.

About the Advanced Solutions Group

A specialized analytics team in the Analytics Cloud product organization that provides world-class technical leadership and guidance focused on empowering the Wave ecosystem. ASG helps drive rapid value by leveraging deep technical knowledge of the Wave platform and apps.

Published
May 9, 2016

Leave your comments...

Customizing the Sales Wave Dashboard for Pipeline Trends