"Moving Metrics" + Date Operations in MBQL

Hello guys, actually, before requesting features, I would like to check if it's not available already.
Like an example, I have a NPS metric -> (Promoters / Detractors) / Responses.
I've created this Metric on Data Modelling, and it is working fine.

I would like to have another metric that calculates this metric always for the last 10 days. Ex: for data reference 20th October, it summarizes the NPS metric for the last 10 days (compared to 20th October) and returns the value to the 20th October dimension. In this dimension it could be a daily time serie , always calculating the last 10 days of the given date -> in SQL I can do that, so I'm asking that for the no-code interface.

Is that possible? Is it available the option to manipulate dates in MBQL, such as date_add?

Thanks guys.

Hi @lvgiacomin
I'm unsure what exactly you are asking for, but you can use relative date filters in Metrics. Or even more advanced custom expressions like How to set date range for comparing this year and last year (to date)
Though, note you might hit this if you're using multiple Metrics:
https://github.com/metabase/metabase/issues/25455 - upvote by clicking :+1: on the first post

Hello @flamber, thanks for the answer.
It does help me, but the problem is that the relativeDateTime compares de argument with the current date. I would like it to compare the argument.

Just an example of a "10 days Rolling count" Grouped By ReferenceDate (Day):
10/nov: if ReferenceDate between 31/out and 10/nov, count
11/nov: if ReferenceDate between 1/nov and 11/nov, count
12/nov: if ReferenceDate between 2/nov and 12/nov, count.
It is always reducing 10 days from the referenceDate, and counting everything within the interval, making a time serie of "10 days Rolling Count".

Did I make it clearer?

@lvgiacomin Perhaps it would be easier if you included the SQL, so it's easier to understand what you are trying to do in the GUI.
I don't know if you are basically just looking for this:

CountIf(interval([ReferenceDate], -10, "day"))

https://www.metabase.com/docs/latest/questions/query-builder/expressions-list#interval

Hey @flamber, I've took a clearer example here, that in the first query I calculate NPS daily, and I summarize it making the average of the last 10 days. Other cases I have here is similar to that, but I would like to change the aggregation (from AVG to SUM, COUNT, ...) and the preceding rows. I think is something similar to "Culumative sum of", but with different aggregations, and not with all values from the serie.

SELECT
    reply_date,
    NPS,
    AVG(NPS) OVER (ORDER BY reply_date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS nps_average
FROM
    (
    SELECT 
        CAST(reply_date AS Date) AS reply_date,
        round(((CAST((sum(CASE WHEN nps_table."category" = 'Promoter' THEN 1 ELSE 0.0 END) - sum(CASE WHEN nps_table."category" = 'Detractor' THEN 1 ELSE 0.0 END)) AS double) / CASE WHEN count(*) = 0 THEN NULL ELSE count(*) END) * 100)) AS "NPS"
    FROM nps_table
    GROUP BY 1
    ORDER BY 1
    ) AS nps_daily
GROUP BY 1, 2

@lvgiacomin Okay, you are using windowing, which is currently not supported in the GUI:
https://github.com/metabase/metabase/issues/9393 - upvote by clicking :+1: on the first post

@flamber thanks for that, I've upvoted. Do you think there are any chances for that to be prioritized?

@lvgiacomin Metabase supports like 20 different database types and requires a lot of work to implement something like that. I cannot give you a timeline on any issues, there's 2,200 other issues.

@flamber yes, I understand it perfectly. Thanks!!