Question

Building of an MAQL for Rolling Average

  • 22 December 2021
  • 5 replies
  • 111 views

We have a table with several records where we need to perform the moving average of the last 90 days considering only grouping by one column (fluxo_id_card).

If we don't consider the rolling avarage, the calculation is performed as follows:

But what happens is that we need to display these results in a month-to-month progression, where each month we view the average of the last 90 days.

For the Rolling average, the basic syntax is:

But we don’t have a certain number of lines that each card would occupy, and that Rows Between does not currently support the COUNT function.

How can we adapt this calculation to the new context without changing the original table in the model?


5 replies

Userlevel 3

Those are awesome news, thank you for your confirmation.

Hi Iva,

 

Yes, they helped us, and we were able the finish the metric and find the values we were looking for. Thank you! 

Userlevel 3

Hey Augusto,

I've been informed by my colleagues that you kindly attended our Office Hours and that you were able to resolve your case by building some new metrics and running average KPIs. Are you still hitting some validation issues, please?

Hi Iva, sorry for the late answer, I was in an end-of-year recess.

 

First of all, we cannot alter our original tables, because other reports depend on this tables. We tought about making a series of math operations to achieve a final result.

Following I attached some prints to explain what we plan to do:

For the present data, we would select just the data of the last 90 days, per example, on 2022-03-01, only the data from 2022-01-01, 02-01 and 03-01 would be selected and summed for the same Id Card, and on 2022-04-01, only the data from 02-01, 03-01 and 04-01 would be selected and summed, taking in consideration the IdCard. This sum is the QtHours for each Id Card.

For 2022-03-01, only the data from 2022-01-01 to 2022-03-01 is selected, summed and grouped by Id Card.
For 2022-04-01, only the data from 2022-02-01 to 2022-04-01 is selected, summed and grouped by Id Card.

 In the next step, we would take the average of the Qt Hours for the Reference month. So, for 2022-03-01, we would take the Average of 122, 108 and 59, and for 2022-04-01, we would take the average of 122, 119 and 80.

In this step, we take the Average of Qt Hours for 2022-03-01 (122, 108 and 59).
In this step, we take the Average of Qt Hours for 2022-03-01 (122, 119 and 80).

And in the next step, we just take these Metric Results and build some reports with them. On the following image, the Report examples are a Headline for the Present Reference Month (107, April), and a Line Chart with the values and the months.

 

Thanks for your help.

 

Att.

Userlevel 3

Hey Augusto,

 

First of all, I'd like to know, how do you plan to filter your report/metric for this 90 days period? Should it affect the whole table, please?

Have you tried to to build your RUNAVG metric as navigated here and simply lock it via the BY clause?

Reply