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?
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".
@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:
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 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.