community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
SOLVED

Cumulative Monthly Amounts (more than one line)

Hello, 

 

I am trying to get a running total for six months of data. Each month has its own line. I can use the running total function on January, although how can I get my February cumulative total to then begin with the last line of data in the January running total + the first line of the February column? I attached a picture to clarify below.

 

Thanks in advance!

 

 

Alteryx
Alteryx

Transpose is your friend. The only challenge was to make sure the data appears in the correct order. See below. Example attached.

 

running-total.png

 

Highlighted

Try using a Transpose tool to get your column headers in one field and the data in the next rather than having the months run across as headers - then you can add a sort to get your months in the right order (might need to convert to datetime format first, or rename prior to transpose to 1, 2, 3 etc. for easy sorting), then use a Running Total tool on all values all at once! See attached for a simplified example.

 

Cheers!

NJ

Pulsar

Hi @Stevensonb12 

Absolutely! The trick with this challenge is getting everything into the correct order before the running total. You'll want to transpose your data so that the months are listed vertically, then get all of January to list before all of February, etc., and then run your running total.

image.png

The Tile tool adds a unique value for each month (1 for Jan, 2 for Feb, 3 for March). You could easily use a formula tool to do the same. Then sort first on that numeric value for the month, and then the record ID. Don't set a grouping level, because grouping in the Running total tool will want to alphabetize for you.

Crosstab twice, one to get the original numbers, and one to get the cumulative totals. Then rename the totals columns, and join back together.

 

Let me know if that helps.

 

Cheers!

Esther

Asteroid

One way to do this is to stack the months first, do a running total, and then use the crosstab tool to rearrange the data. I've attached the sample workflow based on the data you shared.

Capture.JPG

Pulsar

@acastelazo @NicoleJohnson @MichalM , great minds thinking alike!!!

 

Loving how we all posted very similar solutions within a few short time frame.

 

@Stevensonb12 , transpose, sort, running total, crosstab, in a nutshell. Slight variance on the nuances of the workflows

Thank you all. I was able to find a solution using all of your input. Much appreciated!!

Alteryx
Alteryx

@Stevensonb12 

Could you please accept one of the solutions when you get a minute so that other users can more easily find the answer? Thanks!

Labels