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
Solved! Go to Solution.
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.
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 |
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!
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.
Thank you @AbhilashR, it works!!
Hi @AbhilashR,
Could help me with a similar problem in my post below?
Thanks!
Igor Valle