Summing number in matrix form

[Jocelyn1]

Hi, 

I would like to stack up the number in a matrix way.

For example:

cell 1 (2020-01) = B1

cell 2 (2020-02) = B2 + C1

cell 3 (2020-03) = B3 + C2 +  D1

cell 4 (2020-04) = B4 + C3 + D2 + E1

cell 5 (2020-05) = B5 + C4 + D3 + E2 + F1

	Input							Output
	A	B	C	D	E	F		A	B	C	D	E	F
	Date	m1	m2	m3	m4	m5		Date	2020-01	2020-02	2020-03	2020-04	2020-05
1	2020-01	100	320	510	321	240	1		100	520	1470	1551	2070
2	2020-02	200	620	630	450	310
3	2020-03	340	120	780	560	440
4	2020-04	480	370	90	700	820
5	2020-05	230	250	112	108	93

Highly appreciate if you have any idea provided on how to solve this problem.

Thank you

[Aaron_Harter]

Hi @Jocelyn1, 

In the attached solution, I've transposed the data and used the Tile tool with Formulas to determine the corresponding data points required for each step of the matrix calculation:

 

Here's a video showing the capabilities of the Tile tool!

Spoiler

[AbhilashR]

Hi @Jocelyn1, thanks for posing this question! It was a fun problem to solve.  At a high level, I readjusted the rows of your matrix by shifting then by one month (using datetimeadd function), and then summarized them across m1,m2,m3,m4,m5 columns. Not sure if this explanation in words makes a whole lot of sense, so I have attempted to pictorially capture it in the image below.

Screenshot of the workflow

Let us know if this solves your query.

[Jocelyn1]

Hi @Aaron_Harter ,

Thanks for your solution.

It works for me!

However, I am unsure how to replicate the same workflow for multiple classes. Example below, I have multiple classes (10-20 classes) in my input data and I would like to output them like the table on the right.

Do you have any idea? Thank you

Input								Output
		A	B	C	D	E	F		A	B	C	D	E	F
	Class	Date	m1	m2	m3	m4	m5		Date	2020-01	2020-02	2020-03	2020-04	2020-05
1	Class A	2020-01	100	320	510	321	240	Class A		100	520	1470	1551	2070
2	Class A	2020-02	200	620	630	450	310	Class B		338	1114	993	678	1638
3	Class A	2020-03	340	120	780	560	440	Class C		30	499	1659	2380	1930
4	Class A	2020-04	480	370	90	700	820
5	Class A	2020-05	230	250	112	108	93
6	Class B	2020-01	338	644	450	222	57
7	Class B	2020-02	470	43	320	512	61
8	Class B	2020-03	500	88	754	83	331
9	Class B	2020-04	48	110	482	300	199
10	Class B	2020-05	205	321	801	90	200
11	Class C	2020-01	30	99	889	890	410
12	Class C	2020-02	400	60	780	64	248
13	Class C	2020-03	710	40	456	345	470
14	Class C	2020-04	670	120	721	680	310
15	Class C	2020-05	880	321	415	179	220

[Jocelyn1]

Hi @AbhilashR,

Thank you! Your method is easy to understand and it works too.

But my actual data is more than the example I posted here, so using formula to set the date will be quite lengthy. 

Thanks for your help!

[AbhilashR · Accepted solution]

Hi @Jocelyn1, if you are generally comfortable with the approach I proposed, we could make the date formula to be more flexible. I am attaching an updated solution using the new data structure you have shared with us. Take a look and let us know if this addresses your ask.

[Jocelyn1]

Thank you @AbhilashR, it works!! 

[igorfv]

Hi @AbhilashR, 

Could help me with a similar problem in my post below?

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Sum-diagonal-values-in-Alteryx/m-p/935...

Thanks!

Igor Valle