Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Cumulative Monthly Amounts (more than one line)

Stevensonb12
5 - Atom

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!

 

 

8 REPLIES 8
MichalM
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

 

NicoleJohnson
ACE Emeritus
ACE Emeritus

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
15 - Aurora
15 - Aurora

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

acastelazo
8 - 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

estherb47
15 - Aurora
15 - Aurora

@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
5 - Atom

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

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

iambhartesh
6 - Meteoroid

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

 

iambhartesh_0-1631344401046.png

 

Labels