Pivot table running total

Hello

I am trying to replicate this pivot table with the values as running total in the monthly columns.

(see screenshot)

The incremental is added (or deducted each month). For example- my data has Lookup ID 50 (row 5) with a value of £6589 at December (the start of 2022). In January, they had a net movement of -£6589, hence the january value of 0.

Thanks

Time Series

Accepted answers

Samantha_Jayne

Alex great catching up with you, as you can see in this workflow, the combinations of Cross Tab, Transpose and Multi-row formula is what you needed based on your input dataset. Enjoy solving.

Pivot Example.PNG Pivot Output.PNG

Pivot Example.yxzp

All comments

Samantha_Jayne

Hi @pangersandmash it may be helpful to provide the view of the starting point, we know where you want to end up, so that's a bonus.

Based on what I see here I would recommend the following tools, crosstab/transpose, formula tool and the running total tool to guide you on your journey.

pangersandmash

Thanks Samantha!

Those are the tools i have been playing around with. Can 'running total' only go down a column? ie. I would have to run total down, then transpose across?

I have got to here... but i need the columns to be 'start 2022', 'start 2022' + Jan, 'start 2022' + Jan+ Feb, 'start 2022' + Jan+ Feb+ Mar etc...

I have attached a data sample. The values i want to sum consecutively is 'ACV'.

bank records alteryx sample.xlsx

Samantha_Jayne

Now seeing your data: I am thinking a transpose to get all your values in a long list (your data already looks transposed for the columns you need), then a multi-row formula (using the group by function by geography / account) to support your business logic , i.e. if x_column = "this" then add, else subtract. etc.

pangersandmash

When you say 'your data already looks transposed', do you mean from my screenshot in the last comment?

I'm not sure how to implement that logic into the tool. Haven't used multi row before...

Samantha_Jayne

Alex great catching up with you, as you can see in this workflow, the combinations of Cross Tab, Transpose and Multi-row formula is what you needed based on your input dataset. Enjoy solving.

Pivot Example.PNG Pivot Output.PNG

Pivot Example.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703