dynamic summarize

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!

month test.yxzp

Data Investigation

Accepted answers

T_Willins

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.

All comments

T_Willins

Hi @spetkae09

Transpose, summarize, cross-tab.

Month test.JPG

month test.yxmd

NickSm

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

PivotAndSum.yxmd

NicholasM

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.

month test.yxmd

spetkae09

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?

rafalolbert

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

Thanks,

Rafal

workflow.yxmd

Purpose

Hi Rafal,

Any ideas for situations when the number of ids varies from one feed to another feed into the Summarize tool? I thought I could keep extra fields just in case it is necessary but Summarize tool gave an error when I had fewer fields than the tool was configured for: "The filed does not exist..."

_______________

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

Purpose

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703