Toad World® Forums

Is there a Running Sum function?


I'm a new Toad Data Point user in the process of migrating old queries into this new environment.

Could someone please tell me if there is a Running Sum function or something equivalent so I can continue adding to a total: Example is Field1 first 4 values would be 1 10 5 3 and Field2 would be 1, 11, 16, 19.

Is there a function that can accomplish this?

Was this a feature in Hyperion Brio? If so can you show me a screenshot of where this is and details on how you used this feature?

I have a screenshot of this function in Hyperion/Brio from our system. It is something that I have not been able to find a resolution to either except trying to use the MovingSum which does not work the same way. It is the "Cume" function from Hyperion/Brio that calculates a running sum and can be set to have a breakpoint based on another column. See the screenshot below showing the Cume function calculating against the 3rd column and using the 1st column as a break point. I have not seen how to do this in Toad either.

Thanks rvaldez3 for confirming that I'm not crazy.

Hyperion/Brio is what I'm migrating from (I think its 20+ years old), so I figured that a newer product like Toad would have something similar. There must be a work around somewhere. I've read posts about "groupings" but that doesn't do the same job that the Cume function does so seamlessly.

dayre25, you can kind of "cheat" the Cume function into TOAD and see if that works for you. I have used the format below to create a Cume type calculation. You just need to ensure the window size (2nd parameter) is large enough to encompass all of your values. The 3rd parameter will determine the Group By so it should allow you to break up where your Sum will complete. See the screenshot below

Formula: MovingSum([LVL_LEN_NO],500,[PROJ_ID_TOP])


I know this works for this example, but I know I had another that I can't find where the Sum did not work correctly. You might give this a shot and see if it works for you.

Thank you very much rvaldez3! Looks like this will work for my project!!

I think I need to find a master list of functions so I can review and "translate" my BQY's into something TOAD friendly.

With gratitude!

I don't see the MovingSum function anywhere in Transform & Cleanse.
When I tried to enter it manually it gave an error..."ORA-00904: "MOVINGSUM": invalid identifier"
I am running in an Oracle environment.

That is odd, I just checked my TDP (v. 5.06.97) and I see the function. See the screenshot below. Just be sure your "Use SQL Transforms" is not checked.

1 Like

Nice! That should do it!