Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!
SOLVED

dynamic summarize

Highlighted
8 - Asteroid

Hey Everyone,

Every month I am provided 12 months of data. Each month the 13th month back drops off the report and I am left with totals for Current 12. That means the column headers change so i need a way to dynamically change the columns that i am summarizing in the summarize tool.alterxy pic.png

I attached sample report to show in the first 12 months i summarize and total each month in the second report all the summarize fields are incorrect. the last one is missing and the most recent needs to be added. 

 

Each month id like to use directory tool to select most current month file and have the summarize be dynamic so i do not have to go into the workflow and change the months within the summarize.

 

Appreciate any help thanks!

Highlighted
13 - Pulsar

Hi @spetkae09 

 

Transpose, summarize, cross-tab.

 

Month test.JPG

 

Highlighted
Alteryx
Alteryx

Hey @spetkae09 

 

To make a summarization a little more dynamic, you can use a combination of the transpose and cross-tab tools.  With this configuration you don't have to hard-code any column names into the calculations, the pivot will happen with whatever date fields come in.

 

Example attached

 

pivot_sum.PNG

Highlighted
Alteryx
Alteryx

Hey @spetkae09 

 

I believe this can be accomplished dynamically using a series of tools. One Transpose, and one Cross Tab. See photo and workflow attached. 

 

Capture.PNG

8 - Asteroid

with each monthly change do i need to manually change the check marks in the transpose each time the months change and drop off and new ones appear?

Highlighted
13 - Pulsar

No because Alteryx makes it easy.  Just make sure "things to count" is unchecked (keeps it out of your data columns) and "Dynamic or unknown columns" is checked (brings in new fields each month).  On my workflow I attached I just copied and pasted the tools to the 2nd input.

Highlighted
8 - Asteroid

thank you!

Highlighted
11 - Bolide
11 - Bolide

Hi @spetkae09,

 

Proposed solution is to:

 

- use the 'Field Info' tool first to get the metadata

- get the id for every field (i understand you'll have equal number of ids every month when you run your workflow)

- dynamically rename columns and the 'Summarize' tool will now be working as expected

- reverse engineer the rename to get the dynamic names as per the new dataframe

 

image1.JPG

 

Thanks,

Rafal

Labels