Logo

Tag Archive for Moving Averages

Make Sense of Your Data with Moving Averages in Einstein Analytics / Tableau CRM

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.

Tableau CRM Einstein Analytics Data Can Be Messy Make Sense of Your Data with Moving Averages Windowing Functions
Are we getting better or worse?

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’.

Sliding Window tableau crm

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).

Windowing function 7 day moving average in tableau crm reset group start end column and format for sliding window

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.

DivisionDateCount
East4/20/2135
East4/21/2152
East4/22/2123
East4/23/2163
West4/20/2142
West4/21/2132
West4/22/2149
West4/23/2128
Keep our moving averages separate!

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.

Making sense of the data witwh windowing functions in tableau crm 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!