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

Carry forward Total of Calculation to next month as input

Whenky
6 - Meteoroid

I am trying to design a model where I want to sum the input values, get the total for the current month and carry forward it to next month as an input. 

 

I need the calculations to start from the current month till end of year will have columns for all 12 Months.

 

NameValueJANFEBMARAPR
A101111
B202222
C303333
D404445

 

Expected Output:

 

A  Month                                 Next Month                            .... till Last Month of current Year.

    Value                                  Previous Month Sum

    Month Column Value          Month Column Value          

    Sum                                    Sum

 

4 REPLIES 4
estherb47
15 - Aurora
15 - Aurora

Hi @Whenky 

 

Are you looking for a YTD type formula, calculated on the month level?

So for January, 1+2+3+4=10

Febraury: 10+1+2+3+4=20 (carry over the 10 from January, and add it to the values for February)

 

Cheers,

Esther

 

(A mockup of your output with some values in there could help as well)

Whenky
6 - Meteoroid

@estherb47  No I'm Expecting Some thing Like for current month:

 

A  AUG     SEP     OCT     NOV     DEC

     10         11          13        16        20

       1          2            3          4          5

     11         13          16        20        25

estherb47
15 - Aurora
15 - Aurora

Hi @Whenky 

 

 

How about this approach.

 

image.pngTo calculate based on the prior month, transpose your data to vertical, and then filter out on the dates that include the current month and beyond. This is done with a few date functions, to pull a text string representing the month, from the text month header.

 

The Value column from the transpose tool becomes the second row in the result, so renamed it with a select tool as Row2.

 

2 Multi-Row formula tools create the totals, and pull in values from the previous month, if later than the current month.

 

Another transpose tool breaks out those new columns into rows, so that we can build the desired table.

 

Rearrange to the correct order with a Sort, and then rebuild your table with Crosstab.

 

image.png

 

Please let me know if that helps.

 

Cheers!

Esther

Whenky
6 - Meteoroid

Thanks @estherb47  It helped.

Labels