0

Running 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.

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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
        )
    Like 1
    • Anurag Malik thanks. I have looked at the MV code. I have similar SQL and was going to try the MV today.

      Like
Like Follow
  • 2 wk agoLast active
  • 2Replies
  • 15Views
  • 2 Following

Product Announcement

Incorta 4.9 is now Generally Available (GA)!!!