Simple calculations on Salesforce Analytics Cloud

When looking at the most searched items on andrewprice.me I am finding that people are trying to find out how to do calculations on Analytics Cloud. For those vistors to the site there are a couple of ways to do calculations on the platform:

  1. Using the compare table, and adding a calculation as a column
  2. Use SAQL / PIGQL and create a calculation.

Compare Table Calulations

To create a compare table calculation it is very simple. Add a column that is a measure.

In this case I have chosen to calculate the sum of all flights.

Once the measure has been added to a column int the compare table , we then click the drop down arrow on the cell and choose add column.

Once the column has been added, we can then press the drop down again and scroll down and select "edit formula and format".

Once selected we have the option to add in a formula in the "Edit Columns" box. Each column is represented like a Excel Spreadsheet using the alphabet to detail the column names in the formula edit box. In this example I have created the below calculation.

Column A x 2 + Column A

Once the calculation has been added in the Formula Editor, press OK and this will be added to the compare table in a seperate column.

Using SAQL / PIGQL to create calculations:

SAQL is the Salesforce Analytics Cloud Query language. We use this query language in conjuntion with the UI framwork what we call the compact form. The SAQL query is used within a step in a dashboard and returns a result to the step , in which the compact form interprets and renders in eclair / the Salesforce Dashboard UI.

The calculation we are going to create is the same calculation we created in the compare table above however we will represent the outcome in a number widget.

  1. Use the cervello Salesforce Analytics Cloud SAQL Tester - (available here: )
  2. Get the Dataset ID using the details here:

Once we have the Dataset ID we can start to build out a query using the SAQL tester. To Start with we build out a query to get the sum of all flights within the dataset. This is a simple query and looks like the below:

Now we have the simple calculation of sum of flights, we now just need to adjust this to take the calculation in the above raw table and add it into the query.

We now add this to a step and then add that step to the number widget within the dashboard JSON. Below you can see this, it is important to make sure that the measures and groupings added / created in the JSON need to also be replicated in the compact form within the step.

Below is the dashboard functioning with the new calculation in SAQL rendering the number widget.

Using a Relative Date Static Step with with a Selection Bind in SAQL Pigql and Compact form Graphs

When building out dashboards for business consumption, there will be in most cases a requirement to have relative date filters and facet across various widgets on the dashboard ( A relative date for example is 3 months from today). A simple way to achieve this maybe to use the Date widget which can be easily added onto the dashboard from the widget toolbar, however another way is to provide a set of buttons that have static date ranges. This can result in a easier dashboard navigation experience, fixed date ranges, and makes it easier for end users to select date ranges that they would like / should only see.

To setup a widget such as the pillbox or the selector widget with relative date ranges you need a static step to drive the dates added as values on the widget. The picture below shows the outcome of a static step controlling two widgets, one widget which is being filtered via the static step in compact form , and the other filtered via pigql.

Below is the static step for the above dashboard. This is the step that is:

  1. Feeding the values of the buttons - everything in the "Values" section is a value,
  2. Detailing what should be displayed on the buttons per value under the "display" tag.

As you can see in each value there are two subordinate values with the parameters of:

  • saqlstepvalue
  • compactstepvalue

In each of the parameters there are the relative date ranges in the syntax required to the specific type of widget / query that will need to render their relative date range filter. The static step filter must be placed in the steps: section on a dashobard.

The below JSON is the code and syntax required to filter a compact form widget where there is no pigql passed to the widget. As you can see in the whole dashboard JSON at the bottom of the page, this filter must be added in the "query" section of a step. The filter is a selection bind in which it takes the value from the selected static step and passes it into the filter for the associated compact form step.

This next script is a pigql query with the outcomes of the query the same as the compact query above. Included in this query as well is a selection bind that takes the relative date selected in the static step and adds it into the query and therefore filters the associated widget.

Now that we have added a static step into our dashboard , and added the associated filters to both types of widgets / queries, we can see how they look in a completed dashboard. This dashboard is the JSON from the image above.

The JSON describes:

  1. Pillbox Widget connected to the static step
  2. Static Step with relative date ranges
  3. Compact from bar chart with relative date filtering
  4. Pigql query bar chart with relative date filtering

How to test a SAQL query on Salesforce Analytics Cloud

One of the most useful tools when implementing Salesforce Analytics Cloud is the SAQL Query Tester. The query tester essential takes simply PigQL or SAQL query and POSTS that query to Salesforce Analytics cloud. The query editor can be found here: SAQL Query Tester

Finding a SAQL Query in Chrome developer tools.

The first step in using the tool to test a query is finding a query that is generated by Analytics Cloud. This can be done by using the Chrome Developer tool, and inspecting a response packet. Using Chrome Developer Tools, we choose Network, teh filter by Remote and look at a response packet after running a Query. In this example shown in the picture we have done a grouping of Carriers on the "ontime" dataset which has pulled back the following query:

Which looks like the following:

 
 

Installing the SAQL Query Tool.

In order for us to test a query on Analytics cloud we need to usethe query editor - SAQL Query Tester

Once on the page drag and drop the bookmarklet onto the chrome browser favourites toolbar. Once you are on a Analytics cloud lens, press the bookmark and it will open up the SAQL tester.

 
SAQLtester.png
 

Querying Analytics Cloud with the SAQL Tester.

Now that we have the query editor open and using the query that we found previously in the Chrome developers tool, we paste it into the input section on the query tester. Should the query have the right syntax it will return a response from the Analytics Cloud, that looks similar to the graph that Analytics Cloud built.

 
 

Watchpoints when using the SAQL Tester

  1. The SAQL tester will only use the dataset ID/Version ID and won’t allow you to use the dataset alias.

  2. You must take out the “ “ from both the start and end of the query when copying from the Dev tools, otherwise the query won’t work

  3. Ensure that the dataset ID has a syntax like the following \"08939303/BF098409380498309:\". Having 1 slash at the start and end of the ID is required for the SAQL Tester to work.

Video

The below video is to show the process of using the SAQL Query Tool.