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:
- Using the compare table, and adding a calculation as a column
- 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.
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.
- Use the cervello Salesforce Analytics Cloud SAQL Tester - (available here: )
- 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.