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

Excel :Data Preparation

harsha384
8 - Asteroid

Hi All,

I need help with the below mentioned question :

 

Input :

    Curreny Year Profit   CURRENY YEAR SALE    
ProductJANFEBMARCHAPRILMAYJUNEJANFEBMARCHAPRILMAYJUNE
A                                1           1              1                          1              1             1              1                                1             1           1              1             1
B                                2           2              2                          2              2             2              2                                2             2           2              2             2
C                                3           3              3                          3              3             3              3                                3             3           3              3             3
D                                4           4              4                          4              4             4              4                                4             4           4              4             4
E                                5           5              5                          5              5             5              5                                5             5           5              5             5
F                                6           6              6                          6              6             6              6                                6             6           6              6

             6

 

Expected Output :

 

ProductKPIMonthValues
ACurreny Year ProfitJAN              1
BCurreny Year ProfitJAN              2
CCurreny Year ProfitJAN              3
DCurreny Year ProfitJAN              4
ECurreny Year ProfitJAN              5
FCurreny Year ProfitJAN              6
ACurreny Year ProfitFEB              1
BCurreny Year ProfitFEB              2
CCurreny Year ProfitFEB              3
DCurreny Year ProfitFEB              4
ECurreny Year ProfitFEB              5
FCurreny Year ProfitFEB              6
ACURRENY YEAR SALEJAN              1
BCURRENY YEAR SALEJAN              2
CCURRENY YEAR SALEJAN              3
DCURRENY YEAR SALEJAN              4
ECURRENY YEAR SALEJAN              5
FCURRENY YEAR SALEJAN              6
ACURRENY YEAR SALEFEB              1
BCURRENY YEAR SALEFEB              2
CCURRENY YEAR SALEFEB              3
DCURRENY YEAR SALEFEB              4
ECURRENY YEAR SALEFEB              5
FCURRENY YEAR SALEFEB              6

 

Please help!

 

Thanks and Regards

Harsh

6 REPLIES 6
Joe_Mako
12 - Quasar

How about the attached?

 

It is fairly dynamic, as can handle any number of KPIs and months, as long as each KPI has the same number of month columns. If there are a different number of columns per KPI, the workflow would need to be adjusted.

 

Reshape.png

Federica_FF
11 - Bolide

Hi!

 

In attachment another way to get same result (love alteryx for that!), made only with transpose, crosstab and union by name tools that should be dynamic and work no matter the number of months in the table.

Joe_Mako
12 - Quasar

Nice work @Federica_FF !

 

I incorporated your Multi-Row Formula step technique into my workflow, bringing it down to 14 steps, I like how you used the used the Group option.

 

Reshape v2.png

Federica_FF
11 - Bolide

That's amazing!

 

In the very begin I used to create a formula that writes the number 1 in each row plus a Running total grouped by month (in this specific case) to create the sequence of numbers, but since I discovered the group option in the Multi-row tool, the Multi-row tool really became my best friend!

 

It can be used like an advanced RecordID that starts again from 1 when the month changes (or in general based on some grouping logic), like the index() table calculation in Tableau partitioned by some dimension in your table.

 

Very powerful!

 

 

harsha384
8 - Asteroid

Thanks Joe_Mako for the solution!

harsha384
8 - Asteroid

Thanks @Federica_FF and @Joe_Mako for the solution. I really admire the group for being so active and providing solutions quickly. I have implemented the combined solution, seems to be more effective.

 

Thanks and regards

Harsh

Labels