Multiple Datasets in Tables with SAQL - ( In Classic Only )

A common request is to have a number of datasets results charted in tabular format. In Analytics cloud we have both a compare table and a raw data table. This post will share how to show two columns of data from different datasets in Analytics Cloud and show them in a compare table and a raw data table.

For the compare table we will write a query for each column and for the raw data table , we will create a single query that will project each column of the raw data table. The data will be the same.. so you can see the results of both of the outcomes.

The datasets we will be using are below:

A common requirement for Analytics Cloud, is pulling in information from the opportunity object and projecting closed/won deals, pipeline and compairing this to a budget amount. For this example , we are going to compare each states, sales and budget.

Here is an example of a Dashboard with an actuals dataset and a compare table with actuals data, this is done decalratively.

Here is the same dashboard with a SAQL query put in place to replicate the compact form query. This is the first step in allowing us to query multiple datasets in the same compare table. 

Next we see the compare table with both Actuals and Budget Datasets.

 
 

Another way to achieve actauls and audgets in the same table is to use a raw data table and use a single PIGQL / SAQL query to project the results into multiple columns. In the example below.. is a Raw data table with data grouped by State with an actuals column.

 
Screen Shot 2016-06-11 at 9.29.01 PM.png
 

ADDITION:

I had some feedback that we would like to see this overtime.... so I have amended the datasets above, to include dates for both Budget and Actuals and have plotted on a Combo Chart. The reason we have plotted on a combo chart in this instance, is beacause we need to plot multiple measures, We can also do this on a timeline chart but we need to write some transform in the code, to make it a dimension vs a measure. Where as the Combo chart will accept multiple measures. Below is an image of this graph, and the SAQL query and Dashboard JSON to recreate.

 
 

Here is the SAQL query used to generate the graph above. In essence we are doing a few steps here:

  • Load Budget dataset
  • Group budget by Date
  • Project the date then the measure that we would like to plot
  • We do the same for our actuals dataset
  • We then Union each of these results
  • We then project the results of both measures and date with the date projected in a format that will allow the compact form ( Dashboard JSON) to read and display the results.

Using the information above we can create a dashboard, below is a exmaple JSON for a dashboard that would inlude the above query and the Combo chart.