Cumulative total - Multi Row Formula (Including current row dynamic result)

[minvestis]

Hi,

Can anyone help to achieve following result in Alteryx?

I attached same data file to make test on it.

Thanks

Mayank

[minvestis]

Attached file contain excel formula for Expected Output and I want to achieve same in Alteryx.

Appreciate help in advance.

Thanks

Mayank

[ShankerV]

Hi @minvestis 

One way of doing this.

However I see a small difference from your output shared.

Many thanks

Shanker V

[minvestis]

Hi Shankar,

Thanks for reply. The solution you provided that only works for 1st record.

I need dynamic solution to get result for all records in my sample file as each row considering total cumulative total from expected output column.

you can check the formulas in each row in my sample file.

Thanks

Mayank

[geraldo]

@minvestis 

Attached is an example workflow.

Print workflow:

[Qiu]

@minvestis 
We can also try with an interative macro as below.

[minvestis]

@geraldo 

Thanks for sharing solution. I checked and it worked when we have only 1 unique project ID.

I tried your solution with adding more records in my sample file and its not giving expected output.

Will you please help to change workflow to achieve result using my sample file? I added more data into it.

Thanks

[geraldo · Accepted solution]

@minvestis 

Substitute the formula from the mult row formula for this one:

if [Month] <= [MonthEnd] and ( IsEmpty([Row-1:ProjectID]) or IsNull([Row-1:ProjectID]) or [ProjectID] != [Row-1:ProjectID]) then ([Total PC%] * [Budget]) - [Running Total]
else ([Total PC%End] * [BudgetEnd]) - [Running Total End]
endif

[minvestis · Accepted solution]

Hi @geraldo,

Thanks for sharing new formula. It worked as expected.

Appreciate your help and support.

Thanks

[minvestis]

Hi @geraldo @Qiu @ShankerV 

How are you?

I just posted new discussion and need help, following is link for that.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Json-data-read-in-Alteryx-Desk...

Will you please check once you have time? Appreciate your help and support in advance.

Thanks

Mayank Patel