Increasing matrix

[alexey_nikityuk]

Hi all,

I need to create increasing matrix like this:

Basically, the amounts on the intersections are the values attributed to the same category of month, but in different pairs. 

I cant put my head around the data structure on the backend before I do crosstab, any advice is highly appreciated.

[Thableaus]

Hi @alexey_nikityuk 

Do you have a sample of your data?

I'd like to know how your data comes to be transformed in this.

Cheers,

[estherb47 · Accepted solution]

Hi @alexey_nikityuk

An iterative macro can turn your data into a matrix like the one you have. Here, I'm starting with 2 columns, one with the Month-Year, and one with the value (I just used integers 1-12)



First, assign a record ID to each row. This will help us crosstab (create the matrix). We're basing the number of times this macro loops on the iteration number (plus one, because the first is considered iteration 0), so the Formula tool creates that field. Then we filter for the first iteration. Grab the first n records, where n represents that iteration number. Those rows are cross tabbed, with that record ID as the column header (to maintain the order of the months). Those headers are renamed with the dynamic rename tool. These records are "written"

Next check if another iteration needs to be run. The summarize tool finds the Maximum Record ID, which is then appended to each row. If the iteration number is less than that Max Record ID, the macro runs again.

Next bunch of rows are sent through the cross tab process, and added underneath the first run. This repeats until all iterations have run.

Let me know if this works.

Cheers!

Esther

[alexey_nikityuk]

Thank you very much Esther!