HR Reporting: MAT WINDOWS

There are almost no abbreviations more important in the world of HR reports than MAT (Moving Annual Total) and MAT (Moving Average Totals).:)) Moving Annual Total calculations allow users to track and analyze various HR metrics such as employee turnover rate, absenteeism rate, performance, etc. whereas Moving Average Totals allow to soften the seasonality.

So, how can we help Anna, our HR lady, calculate Moving Annual Total in HR reports with the new window functions? First, let us start with the simple running total.

Ingredients:

SUMX – allows to sum up the sum of leavers

WINDOW- generates a window to look at. In our case the window shall look at the 1st row going all the way to the current row.

1, ABS- takes the very first row from the summarized table that is defined after

?0, REL- considers the values of the current row.

SUMMARIZE- defines a table to look at through our window.

ORDER BY- orders by the indicated column.

The last line is our measure, the sum of leavers.

As we can see, the running total looks good. But what if we wanted it to restart every year? In this case we need to indicate the field to slice by. Instead of having only one row of dataset referred to as 1 ABS , we gonna have the Jan of each year be always the first row using PARTITIONBY clause.

Now that Anna knows the basics, she can create the MAT Leavers.

She can simply take the running total calculation and replace the first row with the relative number of months to look back at.

Just in case, you wonder, why -11, we look at the 12 months, well, because we look at 11 month back plus the count of the current row. If you want to have on the current row the sum of previous 3 then change the size of the window to look at -12 REL, -1, REL. If you need the average moving totals then simply change the starting SUMX to AVERAGEX. For the reports that look at Moving Totals, Moving Averages by 3, 6, 12 months, it makes sense to create a parameter i.e. Number of Month and use it to navigate through the rows dynamically.

Change the number of relative rows in the WINDOW Calculation to the measure of the created parameter Number of Months Value.

To reduce the seasonality we can use AVERAGEX in the formual above and here you go a chart using a dynamic windows:

The HR can now create MAT Joiners, calculate the difference between Joiners and Leavers and have a better look at the ongoing trends:

And finally, out HR Anna can now calculate the turnover rate/fluctuation rate dividing MAT Leavers by MAT Headcount , visualize that and use it for predicting her future Headcount situation.

Meet you next week when we start a series of posts on using lines, colors, formatting to create a story line, highlight the insights and make visually appealing reports.