We often get the requirement of calculating rolling averages and accumulations of values over a period of time. And we mostly think of calculating these on the frontend (in chart expressions) using the Above() function. This works fine when we have a small amount of data, but when the data grows, the chart that calculates the accumulations or rolling averages starts affecting performance. In such a situation, it is recommended to move the calculation somehow in the script. The As-Of Table helps to achieve rolling averages and accumulations in the script.

Before we start learning about the As-Of Table, let's understand what accumulations are.

An accumulation is the summation of the data from previous x dimension values, and mostly, it is calculated over a time dimension. For example, the following screenshot shows the full accumulation:

You can see in the preceding screenshot that the accumulation value of Feb is the sales value of Jan + Feb. Similarly, for March it is Jan + Feb + March sales.

Now, let's see how we achieve same thing using an As-Of Table.

The As-Of Table is an intermediate table that contains the AsOfMonth field and Month field, which is further connected to the actual months. The As-Of Table is created using the Cartesian join. The following screenshot shows the script for the As-Of Table:

If you observe the preceding screenshot, you will see that we have created dates using the sample script, and then created a month from those dates. Then, that Month table is joined with a new field name, AsOfMonth. This way, we created a Cartesian table.

In a Cartesian table (Month), each value of AsOfMonth will have 12 values for the Month field. But the AsOfMonth value is relevant only when the Month value is lower than that; thus, we added a Where condition to filter unwanted values (0).

Now, when we create a table with AsOfMonth as a dimension and Sum(Sales) as an expression, we get the full accumulation, which we got by using the Above() function in the chart expression. The following screenshot shows the output:

In the script, you will also see that two more fields are created. One is MonthDiff and the other is YearDiff. The MonthDiff field helps to set the accumulation month. So, if you want the accumulation only for the last three months, instead of all the months, you should use the following expression:

Sum({<MonthDiff = {"<3"}>}Sales)

The following screenshot shows an example:

If you want to get the actual values of the sales per month, you should use the following expression:

Sum({<MonthDiff = {"0"}>}Sales)

The following screenshot shows an example:

Similarly, the YearDiff field is used to get the accumulation for the year. The expression for that would be Sum({<YearDiff={“0”}>} Sales).

If you want to create the rolling 3 months average, you should use following expression:

Sum({<MonthDiff = {"<3"}>}Sales) / Count(Distinct {<MonthDiff = {" 
     <3"}>}Month)