Cumulative Monthly Amounts (more than one line)

[Stevensonb12]

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!

[MichalM · Accepted solution]

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

[NicoleJohnson · Accepted solution]

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

[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

[acastelazo · Accepted solution]

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.

[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