Commulative Sum: Not RUNNING TOTAL

Hello Community,

I am unable to implement one scenario in Alteryx, Please provide me solution for below scenario:

We have table having field RATE, using that need to implement new field called FACTOR. In which need to required add previous rows and multiply with RATE.

I tried to implement in Alteryx using Multi-row formula like:

Step1: Add Record ID.
Step2: Multi-Row Formula-

IF [RecordID]=1
THEN ROUND(([RATE]*100),.01)

ELSE ROUND(([RATE]*(100-[Row-1:FACTOR])),.01)

ENDIF

But I am not getting the same results required.

PFA data and field calculation (FACTOR) in excel that needs to implement in Alteryx.

ALTERYX_SAMPLE_DATA.xlsx

Regex

Parse

Macros

Common Use Cases

Help

Dynamic Processing

Accepted answers

Luke_C

Hi @aman_goyal

I was able to do this with an iterative macro. It will calculate one row at a time and pass the previous rows calculations back in which are summarized for use in the current calculation. Let me know if you have any questions.

Hi @aman_goyal

Here's a way to batch it by ID. For the part you highlighted, that is the iterative output of the macro. When working with iterative macros it needs to have a way to know when to stop iterating. In this case, I'm using the record ID field as way to control what record is being processed based on what number iteration it's on. By finding the max record ID we also know what the last iteration number should be. By filtering on Max Record ID >= Iteration, we ensure that once the iteration becomes greater than the record ID no records will pass through the filter, stopping the macro.

FactorCalc.yxzp

All comments

Hi @aman_goyal

Hi @aman_goyal

The below formula was failing because of the highlighted one.

Step1: Add Record ID.
Step2: Multi-Row Formula-

IF [RecordID]=1
THEN ROUND(([RATE]*100),.01)

ELSE ROUND(([RATE]*(100-[Row-1:FACTOR])),.01)

ENDIF

In the above you are just using only last FACTOR to subtract from 100, but you need to sum all the above factor and detect.

You can refer to the @Luke_C solution as he derived it for you when I was also trying to reach the result for you.

Thanks @Luke_C. You are lighting fast.

aman_goyal

Thank you @Luke_C, It works.
But there is one problem, there is one more field named ID, need to do group by that ID. I have tried that but result is not coming correct. In the below screenshot for row 3 it should be:
For RecordId=1, RATE*(100-0.06),
But it is taking (RATE*(100-(0.06+4.37))

PFA files. Can you please help me with this?