topic Re: Rolling 3 month sum in business schema in Dashboards & Analytics Discussions

Rolling 3 month sum in business schema

amreent — Wed, 21 Aug 2024 22:27:44 GMT

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!

Re: Rolling 3 month sum in business schema

anurag — Wed, 21 Aug 2024 23:21:09 GMT

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

Re: Rolling 3 month sum in business schema

amreent — Thu, 22 Aug 2024 00:14:33 GMT

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