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 ABCDEF ABCDEF Datem1m2m3m4m5 Date2020-012020-022020-032020-042020-0512020-011003205103212401 10052014701551207022020-02200620630450310 32020-03340120780560440 42020-0448037090700820 52020-0523025011210893 Highly appreciate if you have any idea provided on how to solve this problem. Thank you

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

Dynamic Processing

Iterative Macro

Accepted answers

AbhilashR

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.

sampleUpdated.yxmd

All comments

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

2020-04-07 data prep sample.yxmd

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.

sample.yxmd

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