topic Re: Carry forward Total of Calculation to next month as input in Alteryx Designer Desktop Discussions

Carry forward Total of Calculation to next month as input

Whenky — Mon, 12 Aug 2019 15:23:14 GMT

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.

Name	Value	JAN	FEB	MAR	APR
A	10	1	1	1	1
B	20	2	2	2	2
C	30	3	3	3	3
D	40	4	4	4	5

Expected Output:

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

Value Previous Month Sum

Month Column Value Month Column Value

Sum Sum

Re: Carry forward Total of Calculation to next month as input

estherb47 — Mon, 12 Aug 2019 15:38:06 GMT

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)

Re: Carry forward Total of Calculation to next month as input

Whenky — Mon, 12 Aug 2019 15:43:49 GMT

@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

Re: Carry forward Total of Calculation to next month as input

estherb47 — Mon, 12 Aug 2019 16:42:51 GMT

Hi @Whenky

How about this approach.

To 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.

Please let me know if that helps.

Cheers!

Esther

Re: Carry forward Total of Calculation to next month as input

Whenky — Tue, 13 Aug 2019 09:42:05 GMT

Thanks @estherb47 It helped.