A Look to the Future with Timeseries – Part 3

In Winter 19 I did a two-part blog series on the (at that time) new SAQL timeseries statement. Yesterday I got an email requesting a 3rd part to the blog series looking at what you can do with timeseries directly in the UI, since this has been enabled after the original blogs were written. I thought that was a good idea, so here we go.

The easy way to time series

The absolutely easiest way to leverage timeseries is by using the smart templated dashboard. With a few clicks, we have a beautiful dashboard to showcase. Let’s use the same avocado dataset we have used in the previous two parts of the blog series. If you don’t have the dataset already then you can find it here.

From Analytics Studio or your app click “Create” in the top right corner and choose “Dashboard”.

You will now get a dialog box up where you can choose between a blank dashboard or a templated dashboard – you probably guessed we will be selecting “Dashboard from Template”.

You now get a lot of different templates to choose from. Notice that some of them have the grey “Smart” tag on them, meaning you just have to make some selections and the dashboard is 100% created for you – pretty darn amazing, right? In the list of templates, you will find one called “Time Series” (it is a Smart template too), which you select and click “Continue”.

What comes next is pretty straightforward. First, we need to choose the dataset, so let’s continue to see how this lovely avocado is doing and select the “Avocado” dataset.

We need to pick minimum of 2 dimensions and luckily we have “Region” and “Type” in the dataset, so select those. These dimensions are used for two different donut charts in the dashboard to allow for more segmentation.

There are a few more options for the measure, so here you need to pick what you want to predict. I am going to choose the “Avg Price” for avocados.

The next selection is a date. As you hopefully remember from the previous two parts of this blog series we do need to do a grouping on a date field. Again our choice is simple there is just one date field in this dataset, so go ahead and pick that.

We can also define if we want to apply a confidence interval. This is the same thing as the parameter predictionInterval mentioned in the first part of this blog series and it will determine the upper and lower bounds of the prediction. I will choose 95%.

The final selection to make is to indicate if we want to use seasonality. In this case, I am going to select “No”.

Now there is just left to click “Looks good, next” in the bottom right corner.

Next, you give the dashboard a name. I have called mine “Timeseries Templated Dashboard”. Thereafter click “Create”.

And voila! After this you have a complete dashboard to utilize as you can see below. You can, of course, go to edit mode of this dashboard and customize any of the charts.

Compare tables for the win (again)

You might already have a great dashboard created and all you want is just to add an additional chart that leverages timeseries. From Spring 19 it is actually possible to avoid SAQL and instead leverage the compare table. If you have followed my blog I am sure you know by now that I absolutely love compare tables – and in Summer 19, we can avoid many clicks with the lovely formula fields.

In my compare table example I have created a new blank dashboard and just quickly added two list widgets; one for ‘type’ and one for ‘region’.

Now let’s go ahead and create a new step for our timeseries char by clicking on the blue “Create Step” button in the top right corner. And pick the ‘Avocado’ dataset.

I am calling this step “Timeseries”. In this step, we need to pick a measure that we want to predict, so I have picked “Avg of Avg Price”. We also need to pick a date grouping, so in the bar select ‘date’ and choose ‘Year-Month’.

Time has now come to add our timeseries function. Instead of switching to compare table we will just add a new bar or grouping by clicking on the “+” button. Notice in the dialog box we can choose “+ Add Formula” instead of picking a dimension from our dataset. Choose to add a formula.

The step has now automatically switched from a chart to a compare table and we can start out with giving our new column a name in the column header. In my case I am calling it “Predict”. I am also choosing a function instead of a formula and you can see the very last option in the drop down list is the timeseries function, which we will select.

When we select predefined functions, we get a few things we need to choose. First is the column we want to predict and since we only have one other column in our table we can only choose “A: Avg of Avg Price”.

The next thing is to select prediction points, this basically means how many months (or if you selected something else in your grouping) do you want to predict. I have selected 6.

The two next options we can define I have chosen to leave the default selection “Auto”. The first option defines if we want to use seasonality and second if we want to pick the prediction model to use.

The last option is if we want to ignore last. What does this mean? Well, imagine the last month we have in our dataset does not have a complete set of data, maybe it only contains data until the 15th in the month. This means we are missing around 15 days of data and by including that month our prediction can be off. In my case, I am assuming the data is complete and leaving the checkbox unchecked.

Once you are done with all the selections go ahead and click “Apply”.

Now you will have your actual column (the A column) and the predicted column (the B column). You can switch back to your chart now, but that will give you two measures. What if you just want to show one measure that contains actuals if you have them and predictions if there are no actuals? Well, we can add one more column (column C) to our compare table by clicking the “+” icon in the top of the panel to the left. This new column C we will call “Price”, so put that in the column header.

We will stick with the formula option and in the formula editor we will leverage the coalesce() function. This function allows us to default to one value but in case it is null we add in another value. The default value will be our actuals (column A) and the alternative value will be our prediction (column B) hence we will type:

coalesce(A, B)

Notice that coalesce is all in lower case but the reference to columns are in upper case. Once you have this added you can hit apply and see column C be updated with the formula.

Next, you can hit the “Close” button in the formula editor. We don’t need the measure “Avg of Avg Price” nor the formula field “Predict”, therefore you can go ahead and hide those two fields or drag them to the “Unused Fields” section. Finally, we need to drag the “Price” field up in the bar length.

Once that is done we can go ahead and pick the timeline chart and click “Done”. Note you can, of course, do any formatting that you wish to the chart.

Now drag your new step into the canvas to complete your timeseries dashboard.

You may have noticed that the compare table does not allow you to leverage all the parameters we can use in SAQL. So if you, for instance, want to add a confidence interval to your timeseries chart you do have to switch to SAQL. However, I would suggest starting your timeseries query from a compare table and once you have done as much you can there, you switch the SAQL mode and finish off the query. I mean why type it all out when you can get most of it handed on a silver platter?

Written by