To GoodData homepage
Solved

Building of an MAQL for Rolling Average

  • 22 December 2021
  • 7 replies
  • 234 views
A

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?

icon

Best answer by Augusto Silva 14 March 2022, 14:24

View original

7 replies

Iva Gasparekova
Rank
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?

Iva
A

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.

Iva Gasparekova
Rank
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?

Iva
A

Hi Iva,

 

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

Iva Gasparekova
Rank
Userlevel 3

Those are awesome news, thank you for your confirmation.

Iva
A

Hi Augusto, 

I am trying to do something similar
Could you solve this?

 

Could you share how you did it?

Thanks in advance!!

Andres

 

 

A

Hi Andress, thank you for your reply.

Actually in Office Hours they helped us to undestand how the Average functions work in GoodData.
We calculated the Average of the time (hours) via the MAQL below, and divided by 24 (to find the value in days). But this value, Fluxo Tempo Qt Horas was calculated by a Python Scripy, that we ran in Airflow. It calculated the time between a card entered and left a column in our KanBan.
So the SLA for one column is the difference between a card entering and leaving it, and the average for that column, is the Average of the SLA. And to select just a time window, we put a filter in the dashboard, to select just thw preferredt ime.

 

 

Reply


Join us on
Welcome to the GoodData Community!Cookie settings