Rolling 3 month sum in business schema

[amreent]

Hi, is it possible to define a rolling sum formula in a business schema? I would like to sum up totals from 2 preceding rows and 'current row' for each row, and based on the dimensions added to the insight, I want this total to update.

This is what I have in the MV but I would like to make this dynamic in a business schema:

SUM(COUNT(DISTINCT A.WO_Line_ID)) OVER (

        PARTITION BY A.Asset_ID 

        ORDER BY A.Year, A.Month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 

    ) AS Sum_ThreeMonths_Rolling_CMs,

Thank you!

[anurag · Accepted solution]

MV is the only way to do rolling sum.

You can find a work around to do something similar but design it without MV in the following video:

https://www.youtube.com/watch?v=8Cpr9GnGR2s&t=1s

https://www.youtube.com/watch?v=wmpgfXQr9Ac

[amreent]

Thank you Anurag! The videos are very helpful, will try to find a work around using them.