Rolling Aggregation with Compare Tables
As most of you know I am a fan of Einstein Analytics’ compare tables, which is why this is not the first blog on the topic. In all honesty they really are powerful as you do not need to know SAQL or touch JSON in order to utilize their power; there are even out of the box calculations you can leverage. I’ve used most of them but for a long time I looked at “sliding window” and secretly thought to myself what is the use case? Well, think of it as rolling aggregation.
The use case I’ll go through is to see the sum of the amount for the past 4 months per month. Meaning for each month I want to see what the sum of amount has been for the past 4 months.
Compare Table basics
So I have my dashboard ready with just a container and a title.
The very first thing to do is of course create a new step. So click on the blue button to get started with that and choose your dataset; I’ve chosen an opportunity dataset.
In the step I want to do two things; change the measure to Sum of Amount and change the grouping to Close Date Year-Month.
Next, we need to change the step to be powered by a compare table. So click the table mode in the top right corner and choose “Compare Table”.
Finally, we just need to clone the Sum of Amount column to be ready for the compare table magic. So next to your measure click the arrow down and choose “Clone Column”.
Compare Table Magic
With the basic settled it’s time to add the rolling aggregation to the table. Remember we want to see the result for the last 4 months on a monthly basis. If you are still a little lost bear with me, it should all make sense in the end.
Clicking on the arrow next to the latter Sum of Amount choose “Edit this Column”.
We now have two columns; column A and column B. Column A will represent the acutal Sum of Amount for that month and column B will contain our calculation.
The very first thing I want to do is rename my B column to “Total Amount Last 4 Months”. In stead of writing my own formula I will choose one of the predefined. So click f(x) and choose “Sliding Window”.
Now let’s take a moment to explain what you are seeing.
- Column represents the measure you want to do your calculation on.
- Function represents the type of calculation you want to do; average, sum, min, max.
- Start means from the current row (also know as row 0) where do you want to start your calculation. If you choose -1 the calculation will start from the row before. If you choose 1 it will start with the row just after.
- End similarly to the start just means which row from row 0 should be the last row to be included in the calculation.
- Reset group is a function you can use if you have multiple groupings. It allows you to reset the calculation for each group. But we won’t use that as we only have one grouping, hence the only selection option is “None”.
As we want to do rolling aggregation I will change the function from “Average” to “Sum”. Further as we are looking to see the result for a 4 month period per month I will set the start row to “-4” and keep “0” as the end row. Remember to click “Apply”.
Looking at the first row it has the same result in column A and B. The reason for this is that there are no minus rows, there are no previous results to sum. The second row sum up row one and two as this is the only results available. However, when you look at 2015-12 it sums up the results for the past 4 months as we wanted.
Now click “Close”. Before we go back to the dashboard we just want to hide column A. So next the “Sum of Amount” click the arrow and choose “Hide”.
Now we can go ahead and click “Done”. The step is now ready to be added to the dashboard. Instead of simply dragging your new step onto the canvas, first drag a chart widget to the canvas.
Once done you can drag your step onto the chart widget. You can choose the graph that best decribes your data and modify the settings. I’ve choosen a timeline but also the bar chart or line chart would work in my eyes.
But that’s all it takes. Hope you can see some interesting use cases for this build in function in the compare table.
Written by
Hi Rikke
Interesting use case. One ques though. I have a filter for year and quarter and I want this chart to give me the same result if I select a year and a quarter. For ex: if I select 2015-07, then it should show me 38,704,881 rather than 19,048,921. Is it possible?
Hi Rikke,
I am new to Einstein analytics and I have the pretty weird requirement from a client but it is related to the same concept of Rolling up the value. Can you please help me to resolve this.
Requirement: I have one parent object and two associated child objects, I want to calculate the sum of all child records of one child object and add it to another child object sum of amount.
eg. A is Parent of X and Y objects, I want to calculate SUM(X1,X2,….,Xn) – SUM(Y1,Y2,…Yn) for A1 record in the table and without using SAQL. Please help me.
Thanks in advance….!!
It sounds like your amounts would be in two different datasets due to the grain. If that is the case you would need to use saql since you need to load two different dataset. But it’s possible.
This is a great tutorial. I needed this to work out a solution. I do have one question however, how would I go about subtracting weeks from each month but only showing month on the line chart?
So I want a total for a month to be the end of that month minus 8 weeks and the same for the previous month up to 6 months. However, I don’t want to group by or show weeks just each month. Any suggestion on how to accomplish that?
Hi, this was really useful…… hoping you can help with a similar query. How can I go about getting Analytics to present the average over any period of time e.g. let’s say I have created a chart to cover activity over a 12 week period. I now looking to add a number widget next to the chart to return an average value.
I would like Analytics to display the average value (as a number) over the time period i happen to be looking at e.g. if i was to change the date range I want Analytics to recalculate the average based on that time period i.e. i’m now looking at week 6, 7 and 8, I now want the average value to be based on this 3 week date period I am now looking at.
Hi Terlok – a solution for this would be to use a compare table. One column to show the amount or row count, second column to show ‘Unique of Day (or Week or Month or Year), the final column to divide the amount or row count by the Unique of Day’ column.
The good thing about using the e.g. unique of week is that this column will show how many weeks worth of data are in the chart/table, even when the end user filters the dashboard.
Thanks for the tip Mark, really useful
Hi Rikke,
This is really helpful, but is there a way to hide the rows that have the partial window? In other words, could you only show the rows with four previous values to reference? In your example this would hide rows 1,2, and 3 since they don’t have four rows to reference.
Hi Chris, just stumbled over the same problem you ran into. Did you find a solution for that?
Hi, I have the same issue, did you guys manage to find a solution for this?
appreciate this post very mcuh!!
Hello Rikke,
How to set intial selection for collapsible compare table