Data is messy

We all have data that makes it hard to spot trends. Sharp peaks and valleys. You know what I’m talking about.

Sometimes grouping by a larger date range – by the week or the month, for instance, can help. In other cases you need another tactic.

Enter Windowing Functions

Tableau CRM includes a number of windowing functions to do running totals, period over period, change from previous etc. These are all great and you can use them within the compare table by editing a column with ease or handwritten using the saql windowing function syntax.

https://developer.salesforce.com/docs/atlas.en-us.bi_dev_guide_saql.meta/bi_dev_guide_saql/bi_saql_functions_windowing.htm

In this particular example of using Moving Averages we want to choose ‘sliding window’.

After choosing “Sliding Window” you have a number of options. You first select the column that you are using to derive this new column from. In this case we are using ‘Count of Rows.’ Then you choose the Function in the next dropdown. You can select from Sum, Min, Max and Average which is what we will select in this example.

Then you have a Start and End – this references the number of rows to go back and the row to ‘End’ on. 0 being the current row. For example, if we want to do a 7 day (in this case it is days because we have grouped our data by day but it would be months or weeks etc. if months or weeks was our most granular grouping level) we would want to ’Start’ -7 (7 rows back) and ‘End’ at 0 (the current row).

We also have the option to ‘reset group’ by a certain column. For instance, if we had 3 different divisions as our first grouping and then grouped by date we wouldn’t want to avg rows from both divisions and get them all mixed together in calculating a moving average. We would want that calculation to ‘reset’ anytime the Division column changed.

Division	Date	Count
East	4/20/21	35
East	4/21/21	52
East	4/22/21	23
East	4/23/21	63
West	4/20/21	42
West	4/21/21	32
West	4/22/21	49
West	4/23/21	28

In this case we only have the one grouping so we do not need to use this reset option.

Click Apply and now we have a moving average column!

Now you can clearly see the difference between the original raw ‘count’ in blue and the new 7 day moving average in orange.

We can immediately tell that we’ve plateaued recently and even have a downtrend over the mid-term and possibly a slight uptrend over the short-term.

Here is how the same function would be applied by hand in SAQL:

avg(sum(A)) over ([-7..0] partition by all order by ('ActivityDate_Year~~~ActivityDate_Month~~~ActivityDate_Day')) as 'MovingAvg'

Conclusion

The windowing function within Einstein Analytics / Tableau CRM is a powerful tool. I’ve used this extensively as it really helps the viewer make sense of the data and spot trends. I’ve often used this with pillbox toggles on dashboards (with bindings in the JSON) that allow the user to easily toggle between viewing the ‘actual’ data, a moving average (providing several different options for periods) and also a cumulative total (add each prior period together).

What do you think? Have you used this feature? Feel like giving it a try now? Would love to hear your thoughts in the comments! Happy analyzing!