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

Summing number in matrix form

Jocelyn1
6 - Meteoroid

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-05
12020-011003205103212401 100520147015512070
22020-02200620630450310       
32020-03340120780560440       
42020-0448037090700820       
52020-0523025011210893       
              

 

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

Thank you

7 REPLIES 7
Aaron_Harter
11 - Bolide

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:

1.PNG 

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

Spoiler
AbhilashR
15 - Aurora
15 - Aurora

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.

AbhilashR_1-1586315765791.png

 

Screenshot of the workflow

AbhilashR_0-1586315432259.png

Let us know if this solves your query.

Jocelyn1
6 - Meteoroid

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      
  ABCDEF ABCDEF
 ClassDatem1m2m3m4m5 Date2020-012020-022020-032020-042020-05
1Class A2020-01100320510321240Class A 100520147015512070
2Class A2020-02200620630450310Class B 33811149936781638
3Class A2020-03340120780560440Class C 30499165923801930
4Class A2020-0448037090700820       
5Class A2020-0523025011210893       
6Class B2020-0133864445022257       
7Class B2020-024704332051261       
8Class B2020-035008875483331       
9Class B2020-0448110482300199       
10Class B2020-0520532180190200       
11Class C2020-013099889890410       
12Class C2020-024006078064248       
13Class C2020-0371040456345470       
14Class C2020-04670120721680310       
15Class C2020-05880321415179220       
Jocelyn1
6 - Meteoroid

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
15 - Aurora
15 - Aurora

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
6 - Meteoroid

Thank you @AbhilashR, it works!! 

igorfv
7 - Meteor

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

Labels