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!

Running Totals Monthly.PNG

Workflow

Accepted answers

MichalM

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

running-total.yxmd

NicoleJohnson

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!

Cumulative Monthly Amounts.yxmd

acastelazo

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.

Running Total by Month.yxmd

All comments

MichalM

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

running-total.yxmd

NicoleJohnson

Cheers!

Cumulative Monthly Amounts.yxmd

estherb47

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.

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

CumulativeOverMonths.yxmd

acastelazo

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.

Running Total by Month.yxmd

estherb47

@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 🙂

Stevensonb12

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

MichalM

@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!

iambhartesh

Adding to the existing solutions, You can also Use Multi-Field Formula tool to generate the cumulative sum like below

IF [_CurrentFieldName_] = 'Jan' THEN [Jan]
ELSEIF [_CurrentFieldName_] = 'Feb' THEN [Jan]+[Feb]
ELSEIF [_CurrentFieldName_] = 'Mar' THEN [Jan]+[Feb]+[Mar]
ELSEIF [_CurrentFieldName_] = 'Apr' THEN [Jan]+[Feb]+[Mar]+[Apr]
ELSEIF [_CurrentFieldName_] = 'May' THEN [Jan]+[Feb]+[Mar]+[Apr]+[May]
ELSEIF [_CurrentFieldName_] = 'Jun' THEN [Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]
ELSEIF [_CurrentFieldName_] = 'Jul' THEN [Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]
ELSEIF [_CurrentFieldName_] = 'Aug' THEN [Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]
ELSEIF [_CurrentFieldName_] = 'Sep' THEN [Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sep]
ELSEIF [_CurrentFieldName_] = 'Oct' THEN [Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sep]+[Oct]]
ELSEIF [_CurrentFieldName_] = 'Nov' THEN [Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sep]+[Oct]+[Nov]
ELSEIF [_CurrentFieldName_] = 'Dec' THEN [Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sep]+[Oct]+[Nov]+[Dec]
ELSE [_CurrentField_] ENDIF

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703