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

Month to month financials by Company - pivot table

sgrabish1
8 - Asteroid

I was provided 12 separate excel documents that had a consolidating financial for that month; I am trying to organize the data so that one entity's performance performance is shown for each month, before repeating for the next entity.

 

I consolidated each of the workbooks into one file, and had the filename added as a field (since it lists the month of the financial).

 

I tried using cross tab and transpose but hit an impasse.

 

Any suggestions greatly appreciated!

 

Stan

 

 

 

 

6 REPLIES 6
Thableaus
17 - Castor
17 - Castor

Hi @sgrabish1 

 

Could you please provide more details (maybe a screenshot of the Cross-Tab Tool configuration) so we can help you better?

 

Cheers,

sgrabish1
8 - Asteroid

Hi @Thableaus,

 

Thanks for reaching out; I have provided both the source data as well as what I wanted the output to look like.  I was able to get Alteryx to consolidate all the workpapers, but didn't know how to finish it out in Alteryx so I exported back into excel, and closed it out.

 

In the Test Info file, that is what I would like the output to resemble coming from Alteryx.  I know it is a cross-tab exercise but could not figure out how to do since there were multiple entities.

 

Appreciate any help here!

 

Thanks.

 

Stan

 

 

danilang
19 - Altair
19 - Altair

Hi @sgrabish1 

 

The data file that you provided doesn't contain enough information for us to be able to figure out the algorythm.  All we can see from this is that column F and G for April from your input end up in month 4 in the output.  F & G for December end up in month 12 in the output. 

 

Questions 

1.  The entity names change from April to Dec, DEF->SEC, ABC->SCR.  Is this only because you manually changed the first row in the sample?  Do we assume that Col G is always ABC for the entire file, or should we match entity names for each month.

2.  What about the other Entities, JO, ST, SHH, HPT, MRS, SC? do you want the monthly breakdown for these as well?

 

Dan

sgrabish1
8 - Asteroid

@danilang Thanks for reaching out.

 

I meant to change the second row to the same as 1st row with ABC and DEF;   I also only showed April and Dec data as wanted to keep file minimal.  done in haste; sorry about that. 

 

I figured I just needed to see the proposed workflow and that it would be applicable to all entities and months, so I only showed a subset of the data.

 

Thanks for follow up! 

 

 

danilang
19 - Altair
19 - Altair

Hi @sgrabish1 

 

Here's a solution

 

WF.png

Once the null get removed, a multi-row formula builds the final sort order to retain the order of categories.  Then it selects all the relevant entity columns and builds the year, month and month numbers.  The transpose-crosstab pair flips the values from Entity X Month to to Month X Entity.  Then there's a sort and clean up to give you

 

Results.png 

 

Note that the results only have values for April and December but does include all the Entities listed.  As you add in more monthly data, the rest of the month columns will fill out.  I also changed dummy names that you put in back to the original ones in your input file  

 

Dan

sgrabish1
8 - Asteroid

Thank you!

 

Stan

Labels