0Running Total in a formula
I am trying to create a formula that needs to use the running total value for an aggregated date. The formula I want is:
(monthlyBudget - runningtotal(payments)) / numberOfRemainingBusinessDays
This gives a "pace" for the amount needed at each day by date for the amount needed to meet the monthly budget.
I can easily create an aggregated insight that has the running total for a measure. The question is how to access that within a formula on the same insight?
I tried using an Incorta table but it does not have the option for running total.
Insight definitions are attached as well as the resulting aggregate table.
I think I could use an MV, but I don't want to have that much data sitting in memory.
-
Hi Marc,
This is not supported in Incorta.
Yes, you can do this using MV.
Sample MV query for doing cumulative total:
SELECT PROD_ID, CALENDAR_YEAR, rev, sum(rev) OVER ( PARTITION BY PROD_ID ORDER BY CALENDAR_YEAR ASC ROWS BETWEEN unbounded preceding AND CURRENT ROW ) AS running_total FROM ( SELECT PROD_ID, CALENDAR_YEAR, SUM(AMOUNT_SOLD) rev FROM SALES.SALES GROUP BY PROD_ID, CALENDAR_YEAR ORDER BY PROD_ID, CALENDAR_YEAR )
-
Anurag Malik thanks. I have looked at the MV code. I have similar SQL and was going to try the MV today.
-
