Commulative Sum: Not RUNNING TOTAL

[aman_goyal]

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.

[Luke_C · Accepted solution]

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. 

[ShankerV]

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?

Also I did not understand the use of yellow box part. Can you please guide me with this?

Also, Is there any other way to do the same without using Macros?

[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 Alteryx files and updated Input file. Can you please help me with this?

Also I did not understand the use of yellow box part. Can you please guide me with this?

Also, Is there any other way to do the same without using Macros?

[Luke_C · Accepted solution]

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. 

[aman_goyal]

Thank you @Luke_C 

It is working!!